Join through a link table
Query a many-to-many relationship by chaining two joins through a middle table. About 10 minutes.
Many on both sides
A student takes many courses, and a course has many students. Neither table can hold a single id for the other: a student row can't point at three courses with one column.
The fix is a third table, a link table. Each row of enrolments holds one pair: a student_id and a course_id. A student in three courses has three rows there. Students and courses are untouched.
Two hops, one join each
To list students with their courses, hop from students to enrolments, then from enrolments to courses. Each hop is a JOIN with its own ON.
Watch the row counts. A student in three courses appears in three rows of the joined result. So counting students after the joins counts enrolments. Use COUNT(DISTINCT s.id) when you want people.
SELECT s.name, COUNT(*) AS courses FROM students s JOIN enrolments e ON e.student_id = s.id GROUP BY s.id, s.name;
| id | name |
|---|---|
| 1 | Asha |
| 2 | Ravi |
| student_id | course_id |
|---|---|
| 1 | 10 |
| 1 | 11 |
| 2 | 10 |
| id | title |
|---|---|
| 10 | Maths |
| 11 | Art |
Start from a link row: student 1 takes course 10.
A student has 3 enrolments. After students JOIN enrolments, how many rows does that student have in the result?
- A3
- B1
- C0
Show the answer
3. One joined row per matching enrolment.
Classmates
The students, courses and enrolments tables describe who takes which course. Show the name of every student enrolled in the course titled Databases, A to Z.
students(id, name) courses(id, title) enrolments(student_id, course_id)
Up to 1,000 students · names and course titles are unique