Q:

Sql: Get Attributes by joining 3 tables

This is my database schema:

I simply want to Select all the appointments of a patient and get the following result set:

I' have tried the following Join:
SELECT [User].user_id,name, surname, [Appointment].day,[Appointment].month,[Appointment].year,[Appointment].hour,[Appointment].minutes 
FROM [User]
LEFT JOIN [Doctor] ON [Doctor].user_id = [User].user_id 
LEFT JOIN [Patient] ON [Patient].user_id = [User].user_id
LEFT JOIN [Appointment] ON [Doctor].doctor_id = [Appointment].doctor_id and [Patient].patient_id = [Appointment].patient_id

But I didn't get the desired output! 

A:

You need to reference the User table more than once, so you can get separate details for the doctor and patient in the same result record.  What you have can only show results when the Doctor and Patient are the same User. 
This further means you need to use table aliases, so you can distinguish which instance of the User table you are referencing.  It also seems like an INNER JOIN is more appropriate than a LEFT JOIN here. 
Since the problem statement indicates a Patient as your starting point, I would put that as your first/FROM table and go from there:
SELECT a.appointment_id "Appointment ID", ud.name + ' ' + ud.surname "Doctor Name",
     up.name + ' ' + up.surname "Patient Name",
     a.Day, a.Month, a.Year, a.Hour, a.Minute
FROM Patient p
INNER JOIN User up ON up.user_id = p.user_id
INNER JOIN Appointments a ON a.patient_id = p.patient_id
INNER JOIN Doctor d on d.doctor_id = a.doctor_id
INNER JOIN User ud ON ud.user_id = d.user_id

Finally, it seems like a single DateTime column is much better suited for the Appointments table than separate Year, Month, Day, etc columns. 