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.FavMusicType = Band.MusicType WHERE NumYearActive > 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 PatientId), the ID of a physician (name the column PhysicianId), 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.
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 PatientId, Physician.Id AS PhysicianId, Patient.FirstName, Patient.LastName, Experience FROM Patient JOIN Physician ON Patient.Email = Physician.Email WHERE Experience > 5
