Save up to $499! Grab all Python courses for $49 or all online courses we’ve ever launched for only $169. Only Feb 11-16. Happy Valentine's!
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. To simplify your query, note that James Ferris has an id = 8.

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.id = 8