Select in Mysql

Asked

Viewed 23 times

0

I have 2 tables city: id/idPreffect/idVice person: id/name The following select searches for me the name of the city and the name of the Mayor and his deputy...

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;

but when one of the two data is null it does not return anything, as I do for If idmayor and/ or idvice is equal to null it returns "Vago" and continues to bring the rest ??

mysql select

  • Exchange the INNER JOIN, implicit when you specify only JOIN, for LEFT OUTER JOIN.

  • To display "Vacant" if the field is NULL use: COALESCE(ppref.nome, 'Vago') as Prefeito, idem for pvice.nome.

  • How would it look to fit in this select a WHERE namePress = named ?

  • But still bringing the same data in the same way, just adding a "filter"

1 answer

1


Use the LEFT OUTER JOIN to bring the data, even when one of them is NULL:

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

Browser other questions tagged mysql select

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