The Many-to-Many Puzzle

The Enrollment Question

I was analyzing a university database. A student can enroll in many courses. A course can have many students. If I tried to put `course_id` in the `students` table, I'd need multiple columns (course_1, course_2...). This is bad design.

The Quest: The Junction Table

Many-to-Many relationships are handled by a third table, often called a **Junction Table**, **Bridge Table**, or **Linking Table**. It holds pairs of foreign keys.

`students` (id, name)

`courses` (id, course_name)

`enrollments` (student_id, course_id) <-- The junction

The Implementation: Joining Through the Middle

To see which students are in which courses, you join both tables to the junction:

SELECT

s.name AS student_name,

c.course_name

FROM students s

JOIN enrollments e ON s.id = e.student_id

JOIN courses c ON e.course_id = c.id;

The "Oops" Moment

I once tried to directly join `students` to `courses` without the junction table. There was no key linking them, and SQL returned an error.

**Pro Tip**: When you see a table with only `_id` columns and no real data (like `name`), it's almost always a junction table.

The Victory

The university admin could now pull enrollment reports, see which courses had low attendance, and identify students who hadn't registered for anything. The many-to-many problem was solved elegantly.

Your Task for Today

Identify a many-to-many relationship in your data (e.g., orders-products, users-permissions). Find the junction table and write a query that joins through it.

*Day 39: Joining a Table to Itself—Self Join.*