Return data difference from two sql tables

Question

I have a table ESCOLA with ID_ESCOLA and NOME_ESCOLA and another table PROFESSORES containing a FK_ID_ESCOLA and NOME_PROFESSOR. I wanted a table that would return me the names of all schools but schools that had no teachers would return with another color. I know how to handle this data, I just don’t know how to assemble the sql.

Thank you all.

Answer 1

Make a COUNT teachers to know when there is and when there is not, example:

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 you come qtdeProfessores = 0, then you display one color, when it comes <> 0 shows another.

Answer 2

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;