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 that were recommended by a physician named James Ferris. Additionally, show the IDs of patients 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

Console

Code editor

Result

TableConsole