在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'

“查询表达式中的语法错误(缺少运算符)'diagnostic.Patient_No ='Patient_No INNER JOIN疾病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.

我确认sick_code都在疾病和诊断表中,一切都应该有效吗?不确定在这次加入期间我是否需要将这些疾病代码添加到select语句中?我尝试将它们添加到我的Select中,这对我来说似乎也没有用。

4 个解决方案

#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.

看起来您没有在FROM语句中加入药物和处方表。

#2

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

您没有为第一个连接中的第二个Patient_No列指定表。它应该是

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."

您还要从两个未加入的表中选择列 - 药物和处方。但是,这应该会给您一个不同的错误:“多部分标识符'Medication.Medication_Desc'无法绑定。”

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过滤器,省略了Medication / Prescription表。

SQL Fiddle

#3

Also add INNER JOINS for the other two tables,

另外为其他两个表添加INNER JOINS,

INNER JOIN Medication ON Medication.<> = Patient.<>

INNER JOIN Prescription ON Prescription.<> = Patient.<>

#4

You can't still be getting the same error.. .What is happening now? try the 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

#1

Medication.Medication_Desc AS Medication,
Prescription.Dosage AS Dosage