Join does not return the expected

Question

Join does not return the expected

Asked 9 years, 2 months ago

Viewed 50 times

0

I have the following scheme:

tblpessoa with fields: idPessoa, name.

tblcity com os campos: idCidade, codCidade, nome, idPessoa(fk da tabela tblpessoa), codigoRegiaoCidade(fk da tabela tblregiaoCidade).

tblregiaoCity with the fields: codeCity, name.

So I want to bring the name of the person, codeCity and codeCity(fk from table tblregiaoCity). And I do the following query, but returns nothing.

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?

2 answers

Browser other questions tagged sql join

You are not signed in. Login or sign up in order to post.

by Ronaldo Filho • **156** points · Answer 1 · 2015-10-14T13:10:02+00:00

0

Great good morning, probably in table tblCidades there is no match of an idPessoa for the codes of the Where filter you are using, but also recommend using the left Join it will let the execution of select faster.

Tries:

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

Thank you for the answer. So, in this case there is the correlation in the Where clause, ie: WHERE c.ID_PESSOA = p.ID_PESSOA. If I take this line "AND c.codigoRegiaoCidade = 89" the query returns some results, if I take this line: "AND c.codCidade = 23" and go back to the previous one, I also get results, but if I leave both, it returns absolutely nothing.

– Tid

2015/10/14 at 13:49
And was it to return something? Oce has in the table some record with codeCity = 23 and c.codeCity = 89 ?

– David Schrammel

2015/10/14 at 13:53
Hello David, yes, there is such data in the comic book.

– Tid

2015/10/14 at 14:02
In the big case, these matches must be in the same record, try putting instead of and the or and see if this condition of codeCity = 23 and codeCity = 89 agree to the same record

– Ronaldo Filho

2015/10/14 at 16:29

by David Schrammel • **2,310** points · Answer 2 · 2015-10-14T13:54:23+00:00

This query works for what you requested! it’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 ;

Analyzing your post and your comment in the already existing answer, what I could notice is that you want a OR and not a AND under the 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 certainly make you understand your mistake.

In case I’m wrong I apologize.