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

出生第三表