sql - SQL内部连接3个表？(SQL Inner-join with 3 tables?)

Question

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

  ask by Bob Sanders translate from so

Answer 1

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