Get to know the data
JOIN revisited
LEFT JOIN
RIGHT JOIN
FULL JOIN
OUTER JOINS
Aliases for tables
20. Joining more tables

Instruction

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.

Exercise

For each room with two beds where there are actually two students, we want to show one row that contains the following information:

  • The name of the first student.
  • The name of the second student.
  • The room number.

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.'

Stuck? Here's a hint!

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;