Using Multiple Inner Joins in SQL

Question

When running the below query I get an error saying

"Syntax error (missing operator) in query expression 'diagnosis.Patient_No = 'Patient_No INNER JOIN Illness ON Illness.Illness_Code = Diagnosis.Illness_Code'

SELECT Patient.Last_Name AS LastName,
       Patient.First_Name AS FirstName,
       Patient.Gender AS Gender,
       Patient.Age AS Age,
       Illness.Illness_Desc AS illness,
       Medication.Medication_Desc AS Medication,
       Prescription.Dosage AS Dosage
FROM Patient
INNER JOIN Diagnosis ON Patient.Patient_No = Diagnosis.Patient_No
INNER JOIN Illness ON Diagnosis.Illness_Code = Illness.Illness_Code
INNER JOIN Prescription ON Patient.Patient_No = Prescription.Patient_No
INNER JOIN Medication ON Prescription.Medication_code = Medication.Medication_code

I confirmed that illness_code are both in Illness and Diagnosis tables and everything should work? Not sure if during this join I need to add these illness codes to the select statement? I tried adding them to my Select and that didnt seem to work for me either.

Answer 1

Medication.Medication_Desc AS Medication,
Prescription.Dosage AS Dosage

Looks like you don't have the medication and prescription tables joined in your FROM statement.

Answer 2

You didn't specify the table for the second Patient_No column in the first join. It should be

INNER JOIN Diagnosis ON Diagnosis.Patient_No = Patient.Patient_No

You are also selecting columns from two tables that you aren't joining on - Medication and Prescription. However, this should give you a different error of "The multi-part identifier 'Medication.Medication_Desc' could not be bound."

The specific error you are getting sounds like the first problem I mentioned. Here's a working SQL filter with the Medication / Prescription tables omitted.

SQL Fiddle