Medical Center database
Non primary-foreign key JOINs
7. Exercise 2
Non-equi JOINs
Non-equi self JOINs
Summary

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

Console

Code editor

Result

TableConsole