SQL Practice 14/26

Many-to-many: joining through a junction table

The JOIN you've used so far links two tables with a straightforward one-to-many shape: one user, many orders. But some relationships go both ways - a student takes many courses, and a course has many students. Neither table can hold that link directly; a course_id column on students would only fit one course per student.

The fix is a third table that exists purely to record the pairings: a junction table. Each row in it means "this student is in this course" - two foreign keys, nothing else. To answer a question that spans the many-to-many relationship, you join through it: student → junction row → course.

There are three tables: students (id, name), courses (id, title), and enrollments (student_id, course_id) - the junction table linking them.

Your task: return each student's name alongside the title of every course they're enrolled in - one row per enrollment.

You'll practice:

Joining through a junction table with two JOIN ... ON clauses
Seeing why many-to-many relationships need a third table, not a shared column

-- Return each student's name and the title of every course they're enrolled in.
SELECT * FROM enrollments;