Select two columns also showing what is not related [duplicate]

Question

Select two columns also showing what is not related [duplicate]

Navigation

#1 by (0 votes)

0

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

EXAMPLE

Thereare33recordsinthetb_doencatable,butwhenIusethefollowingquerry:selecttb_doenca.nome_doenca,count(tb_vacina.nome_vacina)ASqtfromtb_doencajointb_vacinaonnome_doenca=comb_vacinaGROUPBYcomb_vacinaORDERBYcount(tb_vacina.nome_vacina)itreturnsonly30records,probablybecausethereisnorelationshipbetweentb_doencaandthetb_vacinaintherecords...Iwantedtoknowawaytoshowalldiseasesandintherecordsthatthereisnovaccineforadiseaseitisstilldisplayedwith0

tb_vacina

Command

php mysql pdo

asked by anonymous 18.06.2018 / 19:29

1 answer

Paste filtered data from multiple worksheets into a VBA How to play an audio object instance in Safari?

score 0 · Accepted Answer

Use LEFT JOIN , so all records of the tb_doenca main table will be returned, along with those that match them in the tb_vacina helper table through the nome_doenca = comb_vacina test:

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 fictitious games extracted from the game Theme Hospital = D: link

To better understand the use of the various types of JOIN , I always recommend this site: link

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