Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using Multiple Inner Joins in SQL

Tags:

sql

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.

like image 211
Zi0n1 Avatar asked Feb 08 '13 02:02

Zi0n1


People also ask

Can we use multiple inner join in SQL?

It is possible to use multiple join statements together to join more than one table at the same time. To do that you add a second INNER JOIN statement and a second ON statement to indicate the third table and the second relationship.

How do you inner join more than two tables?

We first join table 1 and table 2 which produce a temporary table with combined data from table1 and table2, which is then joined to table3. This formula can be extended for more than 3 tables to N tables, You just need to make sure that SQL query should have N-1 join statement in order to join N tables.

Can we use inner join for 3 tables?

The most common way of joining three tables goes something like this: SELECT * FROM Table1 INNER JOIN Table2 ON Condition INNER JOIN Table3 ON Condition; This uses an inner join, but you can specify your desired join type as with any other join. You can also combine join types if required (example below).


2 Answers

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.

like image 155
justinb138 Avatar answered Sep 20 '22 20:09

justinb138


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

like image 23
Jeremy Wiggins Avatar answered Sep 19 '22 20:09

Jeremy Wiggins