I have a ESCOLA table with ID_ESCOLA and NOME_ESCOLA and another table PROFESSORES that contains a FK_ID_ESCOLA and NOME_PROFESSOR . I wanted a table that would give me the name of all the schools, but the schools that had no teacher would return with another color. I know how to handle this data, I just do not know how to set up sql .
Thank you all.
Try the following:
SELECT Escola.nome_escola, count(Professores.ID)
FROM Escolas
LEFT JOIN Professores
ON Escolas.ID_ESCOLA=Professores.FK_ID_ESCOLA
GROUP BY Escolas.ID_ESCOLA;
Make a COUNT in teachers to know when and when not, eg:
SELECT e.nome_escola, COUNT(p.nome_professor) qtdeProfessores
FROM ESCOLA e
LEFT JOIN PROFESSORES p ON p.ID_ESCOLA = e.ID_ESCOLA
GROUP BY e.nome_escola
That way when it comes with qtdeProfessores = 0, then you show a color, when you see < > 0 shows from another.