在 SQL 中使用多个内部联接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14764844/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Multiple Inner Joins in SQL
提问by Zi0n1
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'
“查询表达式中的语法错误（缺少运算符）'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.
我确认 disease_code 都在 Illness 和 Diagnosis 表中，一切正常吗？不确定在此加入期间我是否需要将这些疾病代码添加到 select 语句中？我尝试将它们添加到我的 Select 中，但这似乎对我也不起作用。
采纳答案by justinb138
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 语句中没有加入药物和处方表。
回答by Jeremy Wiggins
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 过滤器，省略了药物/处方表。
回答by Orangecrush
Also add INNER JOINSfor the other two tables,
还添加INNER JOINS其他两个表，
INNER JOIN Medication ON Medication.<> = Patient.<>
INNER JOIN Prescription ON Prescription.<> = Patient.<>
回答by Charles Bretana
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
