Two joins, and the junction table's columns (here grade) are available along the way. Counting the students of each course is a join plus a group:

SELECT c.title AS course, COUNT(e.student_id) AS students
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.id
GROUP BY c.id, c.title
ORDER BY students DESC, c.title;

Mixing LEFT and INNER joins

Now suppose some courses have no teacher yet (teacher_id is NULL) and the timetable must still show them. The fourth join must be a left join:

SELECT s.name AS student, c.title AS course, t.name AS teacher
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN courses c     ON c.id = e.course_id
LEFT JOIN teachers t ON t.id = c.teacher_id
ORDER BY s.name, c.title;

The opposite mistake is the one that hides in long queries. Start from students with a left join, so that students with no enrollments appear — and then add courses with a plain JOIN:

SELECT s.name, c.title
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
JOIN courses c ON c.id = e.course_id      -- removes the unenrolled students again
ORDER BY s.name, c.title;

For an unenrolled student the left join produced a row with e.course_id NULL; the inner join's condition c.id = NULL is unknown, and the row is dropped. The rule: once a table is optional, everything joined through it must be left-joined too. Change the last join to LEFT JOIN and the unenrolled students come back with a NULL course.

(Standard SQL also allows nesting — LEFT JOIN (enrollments e JOIN courses c ON …) ON e.student_id = s.id — which means "the enrollment-with-course pairs, optionally". It is correct but rarely written; a chain of left joins is easier to read.)

Fan-out: when joins multiply totals

Each one-to-many join multiplies rows. With one child table that is expected. With two independent child tables it is a bug:

-- Customer 1 has 3 orders (total 300) and 2 payments (total 250).
SELECT c.name, SUM(o.amount) AS ordered, SUM(p.amount) AS paid
FROM customers c
LEFT JOIN orders o   ON o.customer_id = c.id
LEFT JOIN payments p ON p.customer_id = c.id
GROUP BY c.id, c.name;

Orders and payments are not related to each other, so every order row was paired with every payment row: 3 × 2 = 6 rows. Each order was counted twice (once per payment) and each payment three times. The query runs without error and the numbers look plausible — which is why this bug survives code review.

Fixes, best first:

• aggregate each child first, then join the totals — a derived table per child (the subqueries module shows it), so each customer meets at most one order-total row and one payment-total row;
• for counts, COUNT(DISTINCT o.id) undoes the multiplication, but there is no safe SUM(DISTINCT …) — two different orders of 100 would collapse into one;
• run two separate queries.

Fan-out is not limited to two children. Joining orders to order_items and then summing orders.shipping_fee counts the fee once per item. Before summing a column, ask which table it lives in and whether the joins after that table can repeat its rows.

Filtering in a long join

Filters still go in WHERE when they apply to a table that must match anyway:

SELECT cat.name AS category, SUM(i.qty * p.price) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p    ON p.id = i.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.order_date >= '2024-01-01' AND o.order_date < '2025-01-01'
GROUP BY cat.id, cat.name
ORDER BY revenue DESC, category;

The date range is written as a half-open interval on the raw column rather than YEAR(o.order_date) = 2024; both return the same rows, but the first can use an index on order_date (the indexes module explains why). If a category with no 2024 sales must still appear, start from categories and left-join the rest — and then the date filter belongs in the ON of the orders join, for the reason in the outer-joins lesson.

Pitfalls

• A table added without a condition linking it to the rest (a cross join).
• An inner join after a left join on the optional side.
• Summing across two independent one-to-many joins (fan-out).
• SUM(DISTINCT …) as a fan-out fix — it merges equal values from different rows.
• Grouping by a name instead of a key when several tables have names.

Key takeaways

• Each JOIN … ON adds one table to the rows built so far; read the chain as a pipeline.
• Inner join order does not change the result; outer join order does.
• Many-to-many relationships go through a junction table and need two joins.
• Everything joined through an optional table must be left-joined.
• Two independent child tables multiply each other's rows; aggregate each before joining.