Select multiple tables at the same time in SQL [closed]

Question

Select multiple tables at the same time in SQL [closed]

Navigation

#1 by (2 votes)
#2 by (2 votes)
#3 by (0 votes)

-1

I have 5 tables on my system:

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

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

I am making a report for when I finish registering, doing anamnesis, evaluating, intervening with the patient and giving his contract, I pull all the information that is in anamnese , avaliacoes , intervencao % of the patient.

But I do not know how to make a% of it. My teacher helped me by giving an example, but that only takes the patient's information and anamnesis:

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

Where contrato and select are written the aliases of the PAC and FIC tables. I want to have the same thing as this pacientes , but include the rest of the tables.

sql

asked by anonymous 09.11.2018 / 02:47

3 answers

Axis x minute by minute [closed] Use value of a function

score 2 · Answer 1

You can read the documentation about INNER JOIN and LEFT JOIN. A simple idea to put the tables together will be something like

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

In this case, you return all the patients that you have in the anamnesis table. To join more tables would be the case of adding 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 with the most records to the left of the ON. In the documentation there are many other tips that you should take into account.

score 2 · Answer 2

If you want to display all the records in 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]

. . . All the fields in the table, however, you can add the desired columns if you do not want to bring them all at a later time.

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

score 0 · 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 fields of the tables, eg: pac.Name, pac.Ity, int.SomeCountry, cont.OutraCoisa.