Medical Center database
Non primary-foreign key JOINs
8. Joining more tables
Non-equi JOINs
Non-equi self JOINs
Summary

Instruction

Great! Nothing stops us from joinging three or more tables in this manner.

So far, you've worked with the patient_id column. As it turns out, this is actually a foreign key of the therapy table. Suppose that in our previous query, we would like to show the treatment type that has been recommended for each patient instead of showing their ID.

In that case, we need to add another JOIN:

SELECT
  treatment_type,
  physician.id AS physician_id,
  patient.first_name,
  patient.last_name,
  experience
FROM patient
JOIN physician
  ON patient.email = physician.email
JOIN therapy
  ON patient.id = patient_id
WHERE experience > 5

Exercise

For each patient, list the treatment that they have been recommended. Select the ID and first and last name of each patient. Additionally, show the type of treatment that the patient has been recommended along with a list of the names of available treatment sessions. Only list treatments that are cheaper than $100.

Stuck? Here's a hint!

This one's a bit tricky.

First, you have to JOIN the patient table with the therapy table to identify the treatment type that a patient has been recommended. Then, you need to JOIN the treatment table to that resulting table to find the treatment name and pricing.

Type:

SELECT
  patient.id,
  first_name,
  last_name,
  type,
  name
FROM patient
JOIN therapy
  ON patient.id = patient_id
JOIN treatment
  ON treatment_type = treatment.type
WHERE price < 100

Console

Code editor

Result

TableConsole