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