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
So, instead of joining table
patient on two columns that contains their first names and last names, you can join them the shorter way - using
patient.id AS patient_id,
physician.id AS physician_id,
ON patient.email = physician.email
WHERE experience > 5