Instruction
Good. In a non primary-foreign key JOIN, as in any other JOIN, you can provide conditions to filter the result.
Let's go back to the example with student bands. If you wanted to consider only those bands that were active for more than five years, you would write:
SELECT student.name, band.name FROM student JOIN band ON student.fav_music_type = band.music_type WHERE num_year_active > 5
Exercise
We'd like to know if there are patients and physicians at our clinic who share the same first and last name.
For each patient and physician matching this condition, show the patient's ID (name the column patient_id), the ID of a physician (name the column physician_id), their shared first and last name, and the physician's years of experience. Only list physicians who have more than five years of experience.
Stuck? Here's a hint!
Note that the patients who are also physicians at the same time, have common email column in the patient and physician tables.
So, instead of joining table physician and patient on two columns that contains their first names and last names, you can join them the shorter way - using email column.
Type:
SELECT patient.id AS patient_id, physician.id AS physician_id, patient.first_name, patient.last_name, experience FROM patient JOIN physician ON patient.email = physician.email WHERE experience > 5
