SQL内部连接3个表？

本文翻译自：SQL Inner-join with 3 tables?

I'm trying to join 3 tables in a view; 我试图在一个视图中联接3个表； here is the situation: 情况如下：

I have a table that contains information of students who are applying to live on this College Campus. 我有一张桌子，其中包含正在申请住在此大学校园的学生的信息。 I have another table that lists the Hall Preferences (3 of them) for each Student. 我还有另一个表格，列出了每个学生的Hall Preferences（其中的3个）。 But each of these preferences are merely an ID Number, and the ID Number has a corresponding Hall Name in a third table (did not design this database...). 但是这些首选项仅是一个ID号，并且ID号在第三张表中有一个对应的Hall Name（不是设计此数据库...）。

Pretty much, I have INNER JOIN on the table with their preferences, and their information, the result is something like... 差不多，我在桌子上有INNER JOIN以及他们的偏好和他们的信息，结果是...

 John Doe | 923423 | Incoming Student | 005

Where 005 would be the HallID . 其中005是HallID 。 So Now I want to match that HallID to a third table, where this table contains a HallID and HallName . 所以现在我想将该HallID匹配到第三个表，该表包含HallID和HallName 。

So pretty much, I want my result to be like... 差不多，我希望我的结果像...

 John Doe | 923423 | Incoming Student | Foley Hall <---(INSTEAD OF 005)

Here is what I currently have: 这是我目前拥有的：

SELECT
  s.StudentID, s.FName, 
  s.LName, s.Gender, s.BirthDate, s.Email, 
  r.HallPref1, r.HallPref2, r.HallPref3
FROM
  dbo.StudentSignUp AS s 
  INNER JOIN RoomSignUp.dbo.Incoming_Applications_Current AS r 
    ON s.StudentID = r.StudentID 
  INNER JOIN HallData.dbo.Halls AS h 
    ON r.HallPref1 = h.HallID

---

#1楼

参考：https://stackoom.com/question/gmIl/SQL内部连接-个表

---

#2楼

If you have 3 tables with the same ID to be joined, I think it would be like this: 如果您有3个具有相同ID表要联接，我想它将是这样的：

SELECT * FROM table1 a
JOIN table2 b ON a.ID = b.ID
JOIN table3 c ON a.ID = c.ID

Just replace * with what you want to get from the tables. 只需将*替换为要从表中获取的内容即可。

---

#3楼

You just need a second inner join that links the ID Number that you have now to the ID Number of the third table. 你只需要一个第二内加入该链接的ID Number ，你现在必须将ID Number的第三表。 Afterwards, replace the ID Number by the Hall Name and voilá :) 之后，将ID Number替换为Hall Name和voilá:)

---

#4楼

You can do the following (I guessed on table fields,etc) 您可以执行以下操作（我猜在表字段上，等等）

SELECT s.studentname
    , s.studentid
    , s.studentdesc
    , h.hallname
FROM students s
INNER JOIN hallprefs hp
    on s.studentid = hp.studentid
INNER JOIN halls h
    on hp.hallid = h.hallid

Based on your request for multiple halls you could do it this way. 根据您对多个礼堂的要求，您可以采用这种方式。 You just join on your Hall table multiple times for each room pref id: 您只需为每个房间偏好的ID多次加入Hall表：

SELECT     s.StudentID
    , s.FName
    , s.LName
    , s.Gender
    , s.BirthDate
    , s.Email
    , r.HallPref1
    , h1.hallName as Pref1HallName
    , r.HallPref2 
    , h2.hallName as Pref2HallName
    , r.HallPref3
    , h3.hallName as Pref3HallName
FROM  dbo.StudentSignUp AS s 
INNER JOIN RoomSignUp.dbo.Incoming_Applications_Current AS r 
    ON s.StudentID = r.StudentID 
INNER JOIN HallData.dbo.Halls AS h1 
    ON r.HallPref1 = h1.HallID
INNER JOIN HallData.dbo.Halls AS h2
    ON r.HallPref2 = h2.HallID
INNER JOIN HallData.dbo.Halls AS h3
    ON r.HallPref3 = h3.HallID

---

#5楼

SELECT column_Name1,column_name2,......
  From tbl_name1,tbl_name2,tbl_name3
  where tbl_name1.column_name = tbl_name2.column_name 
  and tbl_name2.column_name = tbl_name3.column_name

---

#6楼

This is correct query for join 3 table with same id** 这是对具有相同ID的联接3表的正确查询**

select a.empname,a.empsalary,b.workstatus,b.bonus,c.dateofbirth from employee a, Report b,birth c where a.empid=b.empid and a.empid=c.empid and b.empid='103';

employee first table. 员工第一张桌子。 report second table. 报告第二张表。 birth third table 出生第三表