Medical Center database
Non primary-foreign key JOINs
6. Exercise 1
Non-equi JOINs
Non-equi self JOINs
Summary

Instruction

Good job! It's your turn to write a query with non primary-foreign key equality.

Exercise

We want to know if there are any patients and physicians who have the same email in our clinic. Perhaps physicians also attend our clinic or former patients were hired by the clinic?

List the first and last name of each patient (name the columns patient_first_name and patient_last_name) and the first and last name of each physician (name the columns physician_first_name and physician_last_name) for patients and physicians who share the same email address.

Stuck? Here's a hint!

Type:

SELECT
  patient.first_name AS patient_first_name,
  patient.last_name AS patient_last_name,
  physician.first_name AS physician_first_name,
  physician.last_name AS physician_last_name
FROM patient
JOIN physician
  on patient.email = physician.email

Console

Code editor

Result

TableConsole