Select multiple tables at the same time in SQL

Question

I have 5 tables in my system:

pacientes - Primary key idPacientes
anamnese - foreggin key idPacientes
avaliacoes - foreggin key idPacientes
intervencao - foreggin key idPacientes
contrato - foreggin key idPacientes

The table pacientes has the primary key called idPacientes, the others have foreign keys connected to the patient.

I am making a report for when I finish registering, make anamnesis, evaluate, intervene with the patient and give your contract, I pull all the information that are on anamnese, avaliacoes, intervencao and contrato of the patient.

But I don’t know how to make one select of this. My teacher helped me by giving an example, but that only takes information from patients and anamnesis:

SELECT PAC., FIC.
FROM PACIENTES AS PAC INNER JOIN anamnese AS FIC ON PAC.idPacientes=FIC.idPacientes
where pac.idPacientes = '$id'

Where it is written PAC and FIC are the nicknames of the tables pacientes and anamnese. I want to have the same thing SELECT, but including the rest of the tables.

Answer 1

Can read the documentation on INNER JOIN and LEFT JOIN. A simple idea to join the tables will be something like

Select * from Pacientes INNER JOIN anamnese ON anamnese.idPacientes == Pacientes.idPacientes

In this case you return all patients who have anamnesis in the table. To join more tables would be the case to add another Inner Join

Select * from Pacientes 
INNER JOIN anamnese ON anamnese.idPacientes == Pacientes.idPacientes 
INNER JOIN avaliação ON avaliação.idPacientes == Pacientes.idPacientes 

Always leaving the table that has more records to the left of the ON. In the documentation there are many other tips that you should take into account.

Answer 2

If you want to display all records from each table, you can use ( .* ) to extract all data from each related table as follows:

SELECT 
 Pacientes.*,
 Anamnese.*,
 Avaliação.*,
 Intervenção.*,
 Contratos.* 
FROM Pacientes
 JOIN Anamnese ON Pacientes.idPacientes = Anamnese.idPacientes
 JOIN Avaliacao ON Anamnese.idPacientes = Avaliacao.idPacientes
 JOIN Intervenção ON Anamnese.idPacientes = Intervenção.idPacientes
 JOIN Contratos ON Contratos.idPacientes = Intervenção.idPacientes
WHERE Pacientes.idPacientes = [Id do paciente que você deseja]

Note: the .* behind all fields of the table, however, you can add the desired columns in case you don’t want to bring them all at some later time.

Tip: The tip I give you is to create both tables and columns without graphical accentuation for better maintenance of your project.

Answer 3

SELECT * FROM Pacientes pac
LEFT JOIN anamnese as ana ON ana.idPacientes = pac.idPacientes 
LEFT JOIN avaliacao as ava ON ava.idPacientes = pac.idPacientes 
LEFT JOIN intervenção as int ON int.idPacientes = pac.idPacientes 
LEFT JOIN Contratos as cont ON cont.idPacientes = pac.idPacientes 

Remembering that where * is, you should put the table fields, e.g. Pac.Name, Pac.Age, int.Somerset, cont.Othersisa.