Geek Answers Handbook

Using multiple internal joins in SQL

When I execute the following request, I get an error

"Syntax error (missing statement) in the query expression 'diagn.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 the disease code is both in the Diseases and Diagnosis tables, and should everything work? Not sure if during this connection I need to add these disease codes to the select statement? I tried adding them to my Select, and it didn't seem to work for me either.

+8
sql
source share
4 answers
 Medication.Medication_Desc AS Medication, Prescription.Dosage AS Dosage

It looks like you do not have any drug and prescription tables linked into your FROM statement.

+6
source share

You did not specify a 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 also select columns from two tables that you do not join — medication and prescription. However, this should give you another error: "Multiple identifier" Medication.Medication_Desc "cannot be associated."

The specific error you get sounds like the first issue that I mentioned. The working SQL filter with Medication / Prescription tables does not work here.

SQL Fiddle

+5
source share

Also add INNER JOINS for the other two tables,

 INNER JOIN Medication ON Medication.<> = Patient.<> INNER JOIN Prescription ON Prescription.<> = Patient.<>
0
source share

You still cannot get the same error. What is happening now? try below

  Select p.Last_Name LastName, p.First_Name FirstName, p.Gender Gender, p.Age Age, i.Illness_Desc illness, m.Medication_Desc Medication, s.Dosage Dosage From Patient p Join Diagnosis d On d.Patient_No = p.Patient_No Join Illness i On i.Illness_Code = d.Illness_Code Join Prescription s On s.Patient_No = p.Patient_No Join Medication m On m.Medication_code = p.Medication_code
0
source share

More articles:


All Articles