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:

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.

Answer

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';