Get to know the data
JOIN revisited
LEFT JOIN
RIGHT JOIN
FULL JOIN
OUTER JOINS
Aliases for tables
19. Eliminating duplicates in self-joins

Instruction

Excellent! As you could see, the same table was used twice to apply the join. This is why Jack Pearson was also shown together with Jack Pearson. The database won't check whether the 2 people in the match are the same unless you tell it to do so.

You can add another condition in the WHERE clause:

SELECT
  *
FROM Student AS S1
INNER JOIN Student AS S2
  ON S1.RoomID = S2.RoomID
WHERE S1.Name = N'Jack Pearson'
  AND S1.Name != S2.Name;

Thanks to the second condition, Jack Pearson won't be shown together with himself.

Exercise

We want to know who lives with the student Freya Hart in the same room. Use self-joining to show all information for Freya Hart together with information for each student living with her in the same room.

This time, exclude Freya Hart herself from the result.

Stuck? Here's a hint!

Type:

SELECT
  *
FROM Student AS S1
INNER JOIN student AS S2
  ON S1.RoomID = S2.RoomID
WHERE S1.Name = N'Freya Hart'
AND S1.Name != S2.Name;