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.
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:
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.
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;
