Instruction
Great! Nothing stops us from joining 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 (name it type) 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
