Select from two columns showing also what is not related

Question

Hello, I’m having the following difficulty: I’m using a Select that encompasses two tables tb_illness and tb_vaccine, but it does not display all values.

---

EXAMPLE

On the table tb_illness there are 33 records, but when I use the following querry: select tb_doenca.nome_doenca, count(tb_vacina.nome_vacina) AS qt from tb_doenca join tb_vacina on nome_doenca = comb_vacina GROUP BY comb_vacina ORDER BY count(tb_vacina.nome_vacina) it returns only 30 records, probably because there is no relationship between the tb_illness and the tb_vaccine in the records... I wanted to know a way to show all diseases and in the records that there is no vaccine for a disease she is still displayed with 0

---

tb_vaccine

Commando

Answer 1

Utilize LEFT JOIN, so all the main table records tb_doenca will be returned, along with those matching them in the auxiliary table tb_vacina through the test nome_doenca = comb_vacina:

select 
  tb_doenca.nome_doenca,
  count(tb_vacina.nome_vacina) AS qt 
from 
  tb_doenca left join tb_vacina on nome_doenca = comb_vacina 
GROUP BY comb_vacina 
ORDER BY count(tb_vacina.nome_vacina);

Demonstration in SQL Fiddle using fictional diseases extracted from the game Theme Hospital =D: http://www.sqlfiddle.com/#! 9/0f374e/2

To better understand the use of the various types of JOIN, I always recommend this site: http://www.sql-join.com/sql-join-types/

---

In time, I suggest avoiding establishing relationships between tables using text fields that (apparently) will be entered via typing, such as comb_vacina. A input wrong would end up duplicating records in aggregate queries like this one. Try creating a relationship table with two columns, one with the id of the disease and another with the id the corresponding vaccine. So you have a "watertight" record for each pair "disease <-> vaccine".