Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Medical Center database
Non primary-foreign key JOINs
7. Exercise 2
Non-equi JOINs
Non-equi self JOINs


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:

FROM student
JOIN band
  ON student.fav_music_type = band.music_type
WHERE num_year_active > 5


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.

Note that the patients who are also physicians at the same time, have common email column in the patient and physician tables.

Stuck? Here's a hint!

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.


SELECT AS patient_id, AS physician_id,
FROM patient
JOIN physician
  ON =
WHERE experience > 5