QUERY no expected result, what can be missing?

Navigation
1

Complementing with current information, I understood the current result, and what it looks like is duplicate information, but on different tables, but this is not even with me since I arrived and it was like this and I'm still an intern, I'm just studying this bank. I'm still looking to better understand the use of JOINS.

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

I'mtryingtomakeaqueryasfollows:

SELECTp.idPessoaASID_PESSOA,p.nomeASTITULAR,d.idDependenteASID_DEPENDENTE,d.nomeASDEPENDENTE,c.valorAuxilioASVALOR_AUXILIOFROMsca_pessoapINNERJOINsca_dependentedONp.idPessoa=d.idPessoaINNERJOINsispro_contratocONp.idPessoa=c.idPessoaWHEREp.nomeLIKE'%ADEMARMAGA%'ANDc.idSituacaoContrato=3ANDc.valorAuxilioISNOTNULL;

Iwouldlikeyoutohavethefollowingresult:NAMEHOLDER,DEPENDENTNAMEandAUXILIARYVALUEthatwouldbethevalueofeach(2xpeople)asperthetablebelow.

Thenametablesispro_contratohasthefollowingvalues:

What would be wrong or missing?

If I take the AND c.valorAuxilio ; null returns as follows:

Following 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)
    
asked by anonymous 07.04.2014 / 18:09

2 answers

6

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 it is as follows:

  • The IS NOT NULL comparison is correct. Always use IS NULL and IS NOT NULL instead of < > = . 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?

However, if your database has more than one dependent per person, it will most likely return more than one row per name. Then just grouping, but in case you should not show the name of the dependent, 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
    
07.04.2014 / 18:15
2

If one of these sca_dependente or sispro_contrato tables has more than one record, your query will bring as many person records as there are in those join 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
    
07.04.2014 / 18:37
How to improve code workflow without using synchronous ajax? How to add index in an array