You can also use more than one join in your T-SQL instruction. Let's say we also want to show all the room information for the student pairs with Jack Pearson. Unfortunately, data like room number or floor is not stored in the Student table—we need yet another join with the Room table:
SELECT * FROM Student AS S1 INNER JOIN Student AS S2 ON S1.RoomID = S2.RoomID INNER JOIN Room ON S2.RoomID = Room.ID WHERE S1.Name = N'Jack Pearson' AND S1.Name != S2.Name;
Now that you know self-joins and joining more than two tables, we have a tiny challenge for you.
For each room with two beds where there are actually two students, we want to show one row that contains the following information:
Don't change any column names. Each pair of students should only be shown once. The student whose name comes first in the alphabet should be shown first.
A small hint: in terms of T-SQL, "first in the alphabet" means "less than" for text values. In other words, 'A' is less than 'B.'
Type:
SELECT S1.Name, S2.Name, RoomNumber FROM Student S1 INNER JOIN Student S2 ON S1.RoomID = S2.RoomID INNER JOIN Room ON S1.RoomID = Room.ID WHERE S1.Name < S2.Name AND Room.Beds = 2;
