Joining tables · Lesson 11 of 20

Three-table joins and self joins

From the junction table to the details, and students with their mentors.

9 minutes2 games

To get "student name + course title" you go through the junction table: students → enrollments → courses. Each extra JOIN adds a table to the chain.

SQL

SELECT s.name, c.title, e.progress
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE e.progress = 100
ORDER BY s.name;

Self join: a table with itself

mentor_id in students points at another student in the same table. To get the mentor's name, join the table to itself under two names: s for the student and m for the mentor.

SQL

SELECT s.name AS student, m.name AS mentor
FROM students s
LEFT JOIN students m ON m.id = s.mentor_id
ORDER BY mentor, student;

mentors
- Laila
  - Karim
  - Omar
  - Ali
- Nour
  - Youssef
  - Mariam
- Hana (no mentor, no students)

Order the lines

Order the query that lists each student's name with their course title.

Click the lines in the right order

Exercise

For every course: title and the number of enrolled students as students, including courses with none. Sort by the count, highest first, then by title.

Expected result:

title | students
HTML & CSS | 3
JavaScript | 3
Python | 3
SQL | 2
React | 1
C++ | 0

SQL

SELECT c.title
FROM courses c

Learn more from

Played 0 of 2 games

SQL