QUERY with no expected result, what might be missing?

Question

Complementing with current information, I understood the current result, and what it seems is duplicated information even, but in different tables, but this is not even with me because I already arrived and was like this and I’m still an intern I’m just studying this bank. I’m still trying to better understand the use of JOINS.

===========================================================

I’m trying to make an appointment the following way:

SELECT p.idPessoa AS ID_PESSOA,
       p.nome AS TITULAR,
       d.idDependente AS ID_DEPENDENTE,
       d.nome AS DEPENDENTE,
       c.valorAuxilio AS VALOR_AUXILIO
FROM sca_pessoa p

INNER JOIN sca_dependente d ON p.idPessoa = d.idPessoa
INNER JOIN sispro_contrato c ON p.idPessoa = c.idPessoa

WHERE p.nome LIKE '%ADEMAR MAGA%' AND c.idSituacaoContrato = 3 AND c.valorAuxilio IS NOT NULL;

I would like it to have the following result: NAME HOLDER, NAME DEPENDENT and VALUE OF ASSISTANCE that would be the value of each one (2x persons) as shown in the table below.

The name table sispro_contract has the values as shown below:

What would be wrong or missing?

If I take the AND c.valorAuxilio <> null the return stays like this:

Follow the structures of the tables:

sca_pessoa

idPessoa                     int(10) unsigned
matricula                    varchar(45)
nome                         varchar(100)
telefoneResidencial          char(13)
telefoneCelular              char(13)
telefoneUnidade              char(13)
email                        varchar(100)
dtAdmissao                   date
dtNascimento                 date
idSituacao                   int(4)
SalarioReferencia            int(10) unsigned
idCargo                      int(10)
idLotacao                    int(11)
cpf                          varchar(45)
codigoLotacao                varchar(45)
funcaoValorInicialVertical   varchar(45)
funcaoValorInicialHorizontal varchar(45)
idFuncao                     int(11) unsigned
sexo                         char(1)
rg                           varchar(15)
nomePai                      varchar(44)
nomeMae                      varchar(44)
gsanguineo                   varchar(3)
rh                           varchar(3)
ativo                        char(1)


sca_dependente

idDependente        int(10) unsigned
idPessoa            int(11) unsigned
nome                varchar(100)
dtNascimento        date
idParentesco        int(11) unsigned
pensionista         tinyint(3) unsigned
universitario       tinyint(3) unsigned
pne                 tinyint(3) unsigned
comprovanteEscolar  tinyint(3)


sispro_contrato

idContrato                   int(10) unsigned
idOperadora                  int(10) unsigned
proposta                     varchar(45)
ans                          varchar(45)
valor                        decimal(10,2)
idPessoa                     int(10) unsigned
idFormaDePagamento           int(10) unsigned
idSituacaoContrato           int(10) unsigned
idOperadorUltimaAtualizacao  varchar(45)
dataContrato                 date
dataEncaminhamento           date
dataDeferimento              date
dataHoraUltimaAlteracao      datetime
dataRequerimento             date
dataSituacaoAtual            date
dataSuspensao                date
dataCancelamento             date
idProcesso                   int(11) unsigned
valorAuxilio                 decimal(10,2)
idDependente                 int(10) unsigned
idOperador                   varchar(45)
idTipoOperadora              int(10) unsigned
valorTotalAuxilio            decimal(10,2)

Answer 1

Test like this:

SELECT p.nome AS NOME,
       d.nome AS DEPENDENTE,
       c.valorAuxilio AS VALOR_AUXILIO
FROM sca_pessoa p

-- Mantive o left join, mas no exemplo dado nao vem ao caso.
LEFT JOIN sca_dependente d ON p.idPessoa = d.idPessoa
LEFT JOIN sispro_contrato c ON p.idPessoa = c.idPessoa

WHERE p.nome LIKE '%ADEMAR MAGA%' AND c.idSituacaoContrato = 3 AND c.valorAuxilio IS NOT NULL;

What’s different about that is this:

• The comparison is correct IS NOT NULL.
  Always use IS NULL and IS NOT NULL instead of comparison with < > =. Nulls are not "comparable".

• Fixed the syntax of JOIN and ON

See the difference between JOIN types here:
What is the difference between INNER JOIN and OUTER JOIN?

Anyway, if your database has more than one dependent per person, it will surely return more than one line per name. There just grouping, but in case you should not show the name of the dependent, because it will be more than one:

SELECT p.idPessoa AS ID_PESSOA,
   p.nome AS TITULAR,
   SUM(c.valorAuxilio) AS VALOR_AUXILIO_TOTAL
FROM sca_pessoa p

INNER JOIN sca_dependente d ON p.idPessoa = d.idPessoa
INNER JOIN sispro_contrato c ON p.idPessoa = c.idPessoa

WHERE p.nome LIKE '%ADEMAR MAGA%' AND c.idSituacaoContrato = 3 AND c.valorAuxilio IS NOT NULL
-- agrupar pelo titular:
GROUP BY p.idPessoa
-- OU agrupar pelo titular E dependente (use um dos GROUPs so):
GROUP BY p.idPessoa, d.idDependente

Answer 2

If one of these tables sca_dependente or sispro_contrato have more than one record, your query will bring as many person records as exist in these auxiliary tables.

In this way, I recommend that you group the result by what matters:

SELECT p.idPessoa AS ID_PESSOA,
   p.nome AS TITULAR,
   d.idDependente AS ID_DEPENDENTE,
   d.nome AS DEPENDENTE,
   SUM(c.valorAuxilio) AS VALOR_AUXILIO
FROM sca_pessoa p

INNER JOIN sca_dependente d ON p.idPessoa = d.idPessoa
INNER JOIN sispro_contrato c ON p.idPessoa = c.idPessoa

WHERE p.nome LIKE '%ADEMAR MAGA%' AND c.idSituacaoContrato = 3 AND c.valorAuxilio IS NOT NULL
GROUP BY p.idPessoa,
   p.nome,
   d.idDependente,
   d.nome