CODEWITHSUNDEEP
sqldatabasepostgresql
qutop
qutop

Reputation: 11

Trying to figure out a sql query to fetch the right data from multiple tables

So I have a database and I have 4 tables (admin, appointment, doctor, patient).

I only care about appointment, doctor and patient.

Diagram of these 3 tables:

Diagram of these 3 tables

What I want is based on a patients amka(int) to find all his appointments and show his name, surname and doctors name, surname.

I made this query:

(SELECT doctor.name, doctor.surname
FROM public.doctor
INNER JOIN public.appointment ON doctor.username = 'alouisot2')
union
(SELECT patient.name, patient.surname
FROM public.patient
INNER JOIN public.appointment ON patient.amka = '713783001');

The problem with this query is that it will only show appointments between this specific doctor and I just want to get all the doctors names and surnames.

Also I use postgresql.

I was trying to figure it out but I couldn't. I need to restructure the query but I cannot think how I would solve this problem.

If you have any idea on how to do achieve this I would really appreciate it.

Upvotes: 0

Views: 126

Answers (2)

NickW
NickW

Reputation: 9788

this query just needs simple joins; you seem to have overcomplicated it a lot for some reason...

SELECT 
patient.name, 
patient.surname,
doctor.name, 
doctor.surname
FROM public.patient
INNER JOIN public.appointment 
ON patient.amka = appointment.amka
INNER JOIN public.doctor 
ON appointment.username = doctor.username
WHERE patient.amka = '713783001'

Upvotes: 0

marc_s
marc_s

Reputation: 754618

Try this - properly join the three tables together (doctor to appointment based on the common username column, appointment to patient on the common amka column), and define the WHERE clause with your desired values:

SELECT 
    doctor.name, doctor.surname,
    patient.name, patient.surname
FROM 
    public.doctor
INNER JOIN 
    public.appointment ON doctor.username = appointment.username
INNER JOIN 
    public.patient ON appointment.amka = patient.amka
WHERE
    doctor.username = 'alouisot2'
    AND patient.amka = '713783001';

Upvotes: 0

Related Questions