SQL Inner-join with 3 tables?

Question

I'm trying to join 3 tables in a view; 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. 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...).

Pretty much, I have INNER JOIN on the table with their preferences, and their information, the result is something like...

 John Doe | 923423 | Incoming Student | 005 

Where 005 would be the HallID. So Now I want to match that HallID to a third table, where this table contains a HallID and 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 

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:

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 

Answer 2

If you have 3 tables with the same ID to be joined, I think it would be like this:

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.