Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

JOINs in MS SQL Server

Medical Center database
Non primary-foreign key JOINs
8. Joining more tables
Non-equi JOINs
Non-equi self JOINs
Summary

Instruction

Great! Nothing stops us from joinging three or more tables in this manner.

So far, you've worked with the PatientId column. As it turns out, this is actually a foreign key of the Therapy table. Suppose that in our previous query, we would like to show the treatment type that has been recommended for each patient instead of showing their ID.

In that case, we need to add another JOIN: 

SELECT
  TreatmentType,
  Physician.Id AS PhysicianId,
  Patient.FirstName,
  Patient.LastName,
  Experience
FROM Patient
JOIN Physician
  ON Patient.Email = Physician.Email
JOIN Therapy
  ON Patient.Id = PatientId
WHERE Experience > 5

Exercise

For each patient, list the treatment that they have been recommended. Select the ID and first and last name of each patient. Additionally, show the type of treatment that the patient has been recommended (name it Type) along with a list of the names of available treatment sessions. Only list treatments that are cheaper than $100.

Stuck? Here's a hint!

This one's a bit tricky.

First, you have to JOIN the Patient table with the Therapy table to identify the treatment type that a patient has been recommended. Then, you need to JOIN the Treatment table to that resulting table to find the treatment name and pricing.

Type: 

SELECT
  Patient.Id,
  FirstName,
  LastName,
  Type,
  Name
FROM Patient
JOIN Therapy
  ON Patient.Id = PatientId
JOIN Treatment
  ON TreatmentType = Treatment.Type
WHERE Price < 100