Medical Center database
Non primary-foreign key JOINs
Non-equi JOINs
Non-equi self JOINs
Instruction
Great! How about an exercise to consolidate what we've learned so far?
Exercise
Show the names and types of treatments (name this column Type) that were recommended by a physician named James Ferris. Additionally, show the IDs of patients (as PatientId) for whom these recommendations were made. Exclude treatments of the type Relaxation.
Stuck? Here's a hint!
First, join the Therapy table with the Treatment table on non primary – foreign key condition: use the columns Name, TreatmentType and Type. Then, join the Physician table on physician's ID.
Lastly you need to add filtering conditions. Type:
SELECT Name, Type, PatientId FROM Therapy JOIN Treatment ON Treatment.Type = TreatmentType JOIN Physician ON PhysicianId = Physician.Id WHERE Type != 'relaxation' AND Physician.FirstName = 'James' AND Physician.LastName = 'Ferris';
