How to display records that have no referenced data in a query

Question

How to display records that have no referenced data in a query

Navigation

#1 by (3 votes)

2

I have 3 tables:

Person

CREATE TABLE Pessoa (
    ID_Pessoa INTEGER PRIMARY KEY IDENTITY (1,1),
    ID_Telefone INTEGER FOREIGN KEY REFERENCES Telefone (ID_Telefone),
    Nome_PSOA VARCHAR(50) NOT NULL,
    CPF_PSOA CHAR(14) NOT NULL,
    RG_PSOA VARCHAR(15) NOT NULL,
    Classe_PSOA VARCHAR(30) NOT NULL,
    Cadastro_Pendente_PSOA BIT 
);

Official

CREATE TABLE Funcionario (
    ID_Funcionario INTEGER PRIMARY KEY IDENTITY (1,1),
    ID_Pessoa INTEGER FOREIGN KEY REFERENCES Pessoa (ID_Pessoa) NOT NULL,
    ID_Cargo INTEGER FOREIGN KEY REFERENCES Cargo (ID_Cargo),
    Email_FUNC VARCHAR(50),
    Ramal_FUNC VARCHAR(4),
    Horario_Trabalho_FUNC VARCHAR(50) NOT NULL,
    Escala_FUNC VARCHAR(25),
    Permitir_Visitas_FUNC BIT,
    Descricao_FUNC VARCHAR(200) 
);

Cargo

CREATE TABLE Cargo (
    ID_Cargo INTEGER PRIMARY KEY IDENTITY (1,1),
    ID_Departamento INTEGER FOREIGN KEY REFERENCES Departamento (ID_Departamento) NOT NULL,
    Nome_CRGO VARCHAR(50) NOT NULL,
    Descricao_CRGO VARCHAR(200)
);

In my application I have a query that returns the following data (respectively): Person_ID - EmployeeID - SPOA_name - CRG_name - RG_PSOA - CPF_PSOA

The question is this: When I remove a system post I set NULL in ID_Cargo all Funcionario records that have that job so that I can delete it without any problems of referential integrity. However, when the query is executed, DataGridView returns only records that have a ID_Cargo other than null. My goal is to, in the field Nome_CRGO of all records with ID_Cargo = NULL , show nothing but make them appear in the query.

Query that did not work:

SELECT TOP 20 dbo.Pessoa.ID_Pessoa AS 'ID', 
    ID_Funcionario AS 'ID do Funcionário', 
    Nome_PSOA AS 'Nome', 
    Nome_CRGO AS 'Cargo', 
    RG_PSOA AS 'RG', 
    CPF_PSOA AS 'CPF'
FROM dbo.Pessoa, 
    dbo.Funcionario, 
    dbo.Cargo 
WHERE dbo.Funcionario.ID_Pessoa = dbo.Pessoa.ID_Pessoa 
    AND (
         dbo.Cargo.ID_Cargo = dbo.Funcionario.ID_Cargo 
         OR dbo.Funcionario.ID_Cargo = NULL
        ) 
ORDER BY dbo.Pessoa.ID_Pessoa DESC

sql c#

asked by anonymous 04.06.2018 / 04:06

1 answer

Help me with a foreach array php Syntax error and I do not know where

score 3 · Accepted Answer

(...) When the query is executed, DataGridView returns me only the records that have ID_Cargo other than null

This happens due to the fact that you have defined an expression that will never be true in what would be the declaration of the faculty of existence of the position. Implicitly, you ended up setting INNER JOIN between the 3 tables.

See:

...
dbo.Funcionario.ID_Pessoa = dbo.Pessoa.ID_Pessoa // INNER JOIN entre funcionario e pessoa
     AND (
          dbo.Cargo.ID_Cargo = dbo.Funcionario.ID_Cargo // Implicitamente INNER JOIN entre cargo e funcionario
          OR dbo.Funcionario.ID_Cargo = NULL // Nunca será verdadeiro
         )

This happens because null behaves more like a 'state' than a value.

#.

In summary, in SQL, considering that the ID_Cargo column is as null , the result of the ID_cargo = NULL expression is Falso as well as the ID_cargo <> NULL expression is also. The correct form of comparison in this case would be ID_cargo IS NULL . This would return Verdadeiro as expected.

In particular, I prefer to separate what is a table join criterion (the JOIN clauses declared in FROM ) than the selection condition (those declared in WHERE ). How to proposed by Rovann Linhalis , you could also make a LEFT OUTER JOIN explicitly in the query.

So:

SELECT TOP 20 dbo.Pessoa.ID_Pessoa AS 'ID', 
    ID_Funcionario AS 'ID do Funcionário', 
    Nome_PSOA AS 'Nome', 
    Nome_CRGO AS 'Cargo', 
    RG_PSOA AS 'RG', 
    CPF_PSOA AS 'CPF'
FROM dbo.Pessoa 
    INNER JOIN dbo.Funcionario ON dbo.Pessoa.ID_Pessoa = dbo.Funcionario.ID_Pessoa 
    LEFT JOIN dbo.Cargo ON dbo.Funcionario.ID_Cargo = dbo.Cargo.ID_Cargo
ORDER BY dbo.Pessoa.ID_Pessoa DESC

For this specific example, the query dispenses with the use of WHERE because all conditions refer only to the join criteria of the tables.