Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Get to know the data
JOIN revisited
LEFT JOIN
RIGHT JOIN
FULL JOIN
OUTER JOINS
NATURAL JOIN
Aliases for tables
21. Joining more tables

Instruction

Excellent! You can also use more than one join in your SQL instruction. Let's say we also want to show all the room information for the students paired with Jack Pearson. Unfortunately, data like room number or floor is not stored in the table student – we need yet another join with the room table:

SELECT *
FROM student AS s1
JOIN student AS s2
  ON s1.room_id = s2.room_id
JOIN room ON s2.room_id = room.id
WHERE s1.name = 'Jack Pearson'
  AND s1.name != s2.name;

Now that you know self-joins and joining more than 2 tables, we have a tiny challenge for you.

Exercise

The challenge is as follows: for each room with 2 beds where there actually are 2 students, we want to show one row which contains the following columns:

  • 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 SQL, "first in the alphabet" means "smaller than" for text values.

Stuck? Here's a hint!

Type:

SELECT
  st1.name,
  st2.name,
  room_number
FROM student st1
JOIN student st2
  ON st1.room_id = st2.room_id
JOIN room ON st1.room_id = room.id
WHERE st1.name < st2.name AND beds = 2;