Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Medical Center database
Non primary-foreign key JOINs
9. Filtering results – exercise
Non-equi JOINs
Non-equi self JOINs


Great! How about an exercise to consolidate what we've learned so far?


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:

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