Join does not return as expected

0

I have the following scheme:

tblpessoa with the fields: idPerson, name.

tag with the fields: idCity, codCity, name, idPerformance (fk of the tblpessoa table), codeRegionCity (fk of the tblregiaoCity table).

tblregiaoCity with the fields: codeRegionCity, name.

Then I want to bring the person's name, code, and the Code of Citizenship (fk from the table). And I make the following query, but it does not return anything.

SELECT c.codCidade,
  p.nome,
  c.codigoRegiaoCidade

FROM  tblcidade c,
  tblpessoa p
WHERE c.ID_PESSOA = p.ID_PESSOA
AND c.codCidade = 23
AND c.codigoRegiaoCidade = 89 ;

Does anyone know where the problem is?

    
asked by anonymous 14.10.2015 / 14:50

2 answers

0

Great day, probably in the table tblCities there is no matching of a Person ID to the filter where codes you are using, but I would also recommend using the left join it will make the execution of the select faster.

Try:

SELECT c.codCidade,
       p.nome,
       c.codigoRegiaoCidade

FROM  tblcidade c 
left join tblpessoa p on c.ID_PESSOA = p.ID_PESSOA
where c.codCidade = 23
AND c.codigoRegiaoCidade = 89
    
14.10.2015 / 15:10
0

This query works for what you requested! she's right.

SELECT c.codCidade,
  p.nome,
  c.codigoRegiaoCidade

FROM  tblcidade c,
  tblpessoa p
WHERE c.ID_PESSOA = p.ID_PESSOA
AND c.codCidade = 23
AND c.codigoRegiaoCidade = 89 ;

By analyzing your post and your comment in the existing reply, what I could see is that you want OR and not AND under conditions.

try:

SELECT c.codCidade,
       p.nome,
       c.codigoRegiaoCidade

FROM  tblcidade c 
left join tblpessoa p on c.ID_PESSOA = p.ID_PESSOA
where c.codCidade = 23
OR c.codigoRegiaoCidade = 89

The result of the above query will surely make you understand your error.

If I am wrong, I apologize.

    
14.10.2015 / 15:54