Doubt Select in Mysql

Asked

Viewed 37 times

1

I have two city tables and people City has the columns 

Id/idPrefeito/idVice/nome

And people

Id/nome...

Idmayor and Vice are Foreign Key’s people.id How do I carry in the same select the name of the city, the mayor’s name and the vice president’s name? I’ve already tried:

SELECT cidade.nome, pessoas.nome, pessoas.nome AS Vice
FROM cidade, pessoas
WHERE cidade.idPrefeito = pessoas.id
AND cidade.idVice = pessoas.id

But this code returns nothing to me Exchanging "AND" for || it returns me 2 lines where in 1° Mayor and vice has the name of the Mayor and in 2° Mayor and vice has the name of the Vice, Almost what I want :/

mysql sql select

1 answer

2


Just do the Join twice, one calling idPrefeito and another idVice, using different aliases for each:

SELECT cidade.nome, 
       ppref.nome as Prefeito, 
       pvice.nome AS Vice
FROM cidade
JOIN pessoa ppref ON cidade.idPrefeito = ppref.id
JOIN pessoa pvice ON cidade.idVice = pvice.id;

See here an example working: http://sqlfiddle.com/

  • It worked very thank you friend :)

  • legal, don’t forget to accept the answer

  • A new problem has arisen when idPrefeito and/or idVice this Null in the city table, the code does not return anything... How do I make it return "null" when it is null and return tbm the name the same way it is ?

  • try to change the join for left join

Browser other questions tagged mysql sql select

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