mysql - 如何内连接表？

Question

我有 table

Patient_Detail有 id , name , check_in_id , check_out_id .

Check_Out有 Check_Out_ID 、 Illness_ID 、 Check_Out_Date。

Check_In有 Check_In_ID 、 Illness_ID 、 Check_In_Date 。

Illness有 Illness_ID , Illness_Name .

但问题是我不知道如何加入 illness_Name通过 Check_out表。

USE PantienDatabase

SELECT Name,Check_in,Illness_name,Check_out_ID FROM Check_In AS CI
INNER JOIN Patient_Detail AS P 
ON CI.Check_In_ID = P.Check_In_ID 
INNER JOIN Illness AS I 
ON I.IllnessID  =CI.illness_ID
INNER JOIN 
Check_Out AS CO
ON CO.Check_Out_ID = P.Check_out_ID

Answer 1

您可以连接同一个表两次并为所选列使用别名:

SELECT Name, Check_in,
       II.Illness_name AS Illness_name_in,
       IO.Illness_name AS Illness_name_out,
       Check_out_ID
FROM Check_In AS CI
INNER JOIN Patient_Detail AS P ON CI.Check_In_ID = P.Check_In_ID 
INNER JOIN Illness AS II ON II.IllnessID = CI.illness_ID
INNER JOIN Check_Out AS CO ON CO.Check_Out_ID = P.Check_out_ID
INNER JOIN Illness AS IO ON IO.IllnessID = CO.illness_ID