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 patient_id) 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, treatment_type and type. Then, join the physician table on physician's ID.
Lastly you need to add filtering conditions. Type:
SELECT name, type, patient_id FROM therapy JOIN treatment ON treatment.type = treatment_type JOIN physician ON physician_id = physician.id WHERE type != 'relaxation' AND physician.first_name = 'James' AND physician.last_name = 'Ferris';
