SQL Inner-join with 3 tables?
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
005 would be the
HallID. So Now I want to match that
HallID to a third table, where this table contains a
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
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
Read more... Read less...
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
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
* with what you want to get from the tables.
SELECT table1.col,table2.col,table3.col FROM table1 INNER JOIN (table2 INNER JOIN table3 ON table3.id=table2.id) ON table1.id(f-key)=table2.id AND //add any additional filters HERE
You just need a second inner join that links the
ID Number that you have now to the
ID Number of the third table. Afterwards, replace the
ID Number by the
Hall Name and voilá :)
There have been plenty of responses but the overall lesson seems to be that you can use multiple JOINS in a where clause; also techonthenet.com (my boss recommended it to me, that's how I found it) has good SQL tutorials if you ever have another question and you just want to try and figure it out.
SELECT table1.column1 FROM table1 WHERE table1 > 0 (or whatever you want to specify) INNER JOIN table1 ON table1.column1 = table2.column1