Join table with diferent user roles

Question

I am trying to join one table that contains apponintments with a table that contains user info, its a wordpress user table so the user data is stored as a vertical table (not sure about that term)

What I have is something like this:

Appointments table

id	appointment_date	doctor_id	patient_id
1	2023-02-15	02	04
2	2023-02-18	03	04

Users table

user_id	meta_key	meta_value
02	first_name	'doctorname2'
02	last_name	'doctorLname2'
03	first_name	'doctorname3'
03	last_name	'doctorLname3'
04	first_name	'patientname4'
04	last_name	'patientLname4'

With the following Query I can get the doctor or the patient of the appointment but not both

SELECT id, appointment_date, 
       max(CASE WHEN meta_key = 'first_name' THEN meta_value END) AS name,
       max(CASE WHEN meta_key = 'last_name' THEN meta_value END) AS last_name
FROM      appointments
LEFT JOIN usermeta ON doctor_id = user_id
GROUP BY id

id	appointment_date	name	last_name
01	2023-02-15	'doctorname2'	'doctorLname2'
02	2023-02-18	'doctorname3'	'doctorLname3'

I need some help to get this table

id	appointment_date	dr_name	dr_last_name	pat_name	pat_last_name
01	2023-02-15	'doctorname2'	'doctorLname2'	'patientname4'	'patientLname4'
02	2023-02-18	'doctorname3'	'doctorLname3'	'patientname4'	'patientLname4'

I have tried Unions but with no success.

Thanks for helping me.

Answer 1

There's one issue in your query: you can't join your appointments table both on doctors and patients. You need two join operations for that.

Also you're missing the "appointment_date" field inside the GROUP BY clause. There may be errors or subtle issues if all non-aggregated (and selected) fields are not found within that clause.

A minor change: you don't need left joins, assuming that your "Users" table contains all possible users (hence users contained in the appointments table is a subset).

SELECT a.id,
       a.appointment_date,
       MAX(CASE WHEN doctors.meta_key = 'first_name'
                THEN doctors.meta_value              END) AS dr_name,
       MAX(CASE WHEN doctors.meta_key = 'last_name'
                THEN doctors.meta_value              END) AS dr_last_name,
       MAX(CASE WHEN patients.meta_key = 'first_name'
                THEN patients.meta_value             END) AS pt_name,
       MAX(CASE WHEN patients.meta_key = 'last_name'
                THEN patients.meta_value             END) AS pt_last_name
FROM       appointments a
INNER JOIN usermeta     doctors
        ON a.doctor_id = doctors.user_id
INNER JOIN usermeta     patients
        ON a.patient_id = patients.user_id
GROUP BY a.id, a.appointment_date 

Check the demo here.