how can i display the name of a professional from a patient's appointment?

Question

I have this four tables:

One for users, other for professionals, other for patients y another for appointments. I want to get the name of professional from patient's appointment.

I try this:

SELECT appointments.date, appointments.start_time, appointments.indication, professionals.medical_box 
FROM appointments 
INNER JOIN patients ON appointments.id_patient = patients.id 
INNER JOIN users ON users.id = patients.id_user 
INNER JOIN professionals ON professionals.id = appointments.id_professional 
WHERE users.id = 14 
ORDER BY appointments.date DESC, appointments.start_time ASC

And I get all correctly, but when I try to show users.name, the name that I get is the patient name, no the professional name, I want the professional name from that patient appoinment. What will be the sql query?

Answer 1

It looks like to get the professionals name you need to also join the user table on the professional.

Add an alias for the second join onto the users table and reference that when displaying your fields.

SELECT appointments.date, appointments.start_time, appointments.indication, professionals.medical_box, usersProf .name
FROM appointments 
INNER JOIN patients ON appointments.id_patient = patients.id 
INNER JOIN users ON users.id = patients.id_user 
INNER JOIN professionals ON professionals.id = appointments.id_professional 
INNER JOIN users as usersProf ON usersProf .id = professionals.id_user 
WHERE users.id = 14 
ORDER BY appointments.date DESC, appointments.start_time ASC