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
Summary

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