Writing SQL for Many-to-Many Relationships: Junction Tables, Double JOINs, and Aggregate Counts Across Bridge Tables

Introduction

You're building a report that shows how many courses each student is enrolled in, or how many tags have been applied to each product, or which authors have collaborated on which articles. You write what feels like a reasonable JOIN query, and then the numbers come back completely wrong — enrollment counts that are three times too high, products appearing multiple times, totals that make no sense. You haven't made a typo. The problem is structural: you're dealing with a many-to-many relationship, and many-to-many relationships require a different querying pattern than the one-to-many joins you're used to.

Many-to-many is everywhere in real databases. Students enroll in many courses, and courses have many students. Orders contain many products, and products appear in many orders. Employees belong to many teams, and teams have many employees. The database can't store these relationships with just a foreign key on one table — so it uses a third table, called a junction table (also called a bridge table or associative table), to hold the connections. Querying through that junction table requires you to JOIN twice, aggregate carefully, and understand exactly what rows you're counting before you count them.

By the end of this lesson, you'll be able to design junction tables correctly, write double-JOIN queries that traverse many-to-many relationships, aggregate counts through bridge tables without inflation, and filter on both sides of the relationship simultaneously.

What you'll learn:

• What junction tables are, why they exist, and how to recognize them in a schema
• How to write double-JOIN queries to traverse many-to-many relationships
• How to count correctly across bridge tables without inflating results
• How to filter on properties of both sides of a many-to-many relationship in a single query
• How to use CTEs to organize complex multi-step aggregations across junction tables

---

Prerequisites

This lesson assumes you're comfortable with:

• Basic SELECT, FROM, WHERE queries — if not, see SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries
• INNER JOIN and LEFT JOIN fundamentals — brush up at SQL JOINs Explained with Real-World Examples
• GROUP BY and aggregate functions like COUNT() and SUM() — covered in Master SQL Aggregate Functions: GROUP BY, HAVING, COUNT, SUM, AVG

---

What Is a Junction Table and Why Does It Exist?

Before writing queries, you need to understand what you're querying through. Let's use a realistic domain: a corporate learning management system (LMS) that tracks employees, training courses, and skill tags.

Consider the relationship between employees and courses. An employee can enroll in multiple courses. A course can have multiple employees enrolled. That's many-to-many. You can't handle this with just a foreign key on the employees table (current_course_id) because each employee can have many current courses. You can't put a current_employee_id on the courses table for the same reason.

The solution: a third table that holds pairs of IDs, one from each side.

-- The two main tables
CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    full_name     VARCHAR(100),
    department    VARCHAR(50),
    hire_date     DATE
);

CREATE TABLE courses (
    course_id     INT PRIMARY KEY,
    course_name   VARCHAR(150),
    category      VARCHAR(50),
    credits       INT
);

-- The junction table
CREATE TABLE enrollments (
    enrollment_id  INT PRIMARY KEY,
    employee_id    INT NOT NULL REFERENCES employees(employee_id),
    course_id      INT NOT NULL REFERENCES courses(course_id),
    enrolled_at    TIMESTAMP DEFAULT NOW(),
    completed_at   TIMESTAMP,
    UNIQUE(employee_id, course_id)  -- an employee can only enroll once per course
);

The enrollments table is the junction table. It has foreign keys pointing to both employees and courses, and each row represents one relationship between one employee and one course. The UNIQUE constraint on (employee_id, course_id) prevents duplicate enrollments.

Let's also add a skill-tagging system, where courses can be tagged with multiple skills, and skills apply to multiple courses:

CREATE TABLE skills (
    skill_id    INT PRIMARY KEY,
    skill_name  VARCHAR(100),
    skill_level VARCHAR(20)  -- 'Foundational', 'Intermediate', 'Advanced'
);

CREATE TABLE course_skills (
    course_id   INT NOT NULL REFERENCES courses(course_id),
    skill_id    INT NOT NULL REFERENCES skills(skill_id),
    PRIMARY KEY (course_id, skill_id)
);

Note that course_skills uses a composite primary key instead of a surrogate enrollment_id. Both patterns are valid — use a surrogate key when you need to reference the junction row itself (e.g., in another table), and a composite key when the junction row doesn't need to be referenced directly. You can read more about this tradeoff in SQL Data Types and Schema Design Mastery: Performance, Constraints, and Evolution Strategies.

---

The Double JOIN Pattern

To get data from both sides of a many-to-many relationship, you JOIN from one entity to the junction table, then JOIN from the junction table to the other entity. Two JOINs, chained.

Here's the simplest form: show each employee's name alongside each course they're enrolled in.

SELECT
    e.full_name,
    e.department,
    c.course_name,
    c.category,
    en.enrolled_at
FROM employees e
INNER JOIN enrollments en
    ON e.employee_id = en.employee_id
INNER JOIN courses c
    ON en.course_id = c.course_id
ORDER BY e.full_name, en.enrolled_at;

Follow the chain: employees → enrollments → courses. Each JOIN uses the foreign key from the junction table to connect to one side. The result is a row for every employee-course pair that exists.

Now extend it: show employees, their courses, and the skills that each course teaches:

SELECT
    e.full_name,
    c.course_name,
    s.skill_name,
    s.skill_level
FROM employees e
INNER JOIN enrollments en
    ON e.employee_id = en.employee_id
INNER JOIN courses c
    ON en.course_id = c.course_id
INNER JOIN course_skills cs
    ON c.course_id = cs.course_id
INNER JOIN skills s
    ON cs.skill_id = s.skill_id
ORDER BY e.full_name, c.course_name, s.skill_level;

This query traverses two many-to-many relationships: employees↔courses (through enrollments) and courses↔skills (through course_skills). Each employee appears once per combination of course and skill. If an employee is in one course that teaches three skills, they get three rows.

Understanding the row multiplication here is critical. Before you run any aggregation, ask yourself: "What does one row in this result represent?" In the last query, one row = one (employee, course, skill) combination. That understanding prevents counting errors.

---

Counting Correctly Across Junction Tables

This is where most people get burned. Let's count how many courses each employee is enrolled in.

The wrong approach:

-- DON'T DO THIS
SELECT
    e.full_name,
    COUNT(*) AS course_count
FROM employees e
INNER JOIN enrollments en
    ON e.employee_id = en.employee_id
INNER JOIN courses c
    ON en.course_id = c.course_id
GROUP BY e.employee_id, e.full_name;

This actually gives you the right answer in this specific case — but for the wrong reason that will break later. You're counting * (all rows), which works here because each row in the joined result corresponds to one enrollment. But if you add another JOIN downstream (say, to course_skills), suddenly each enrollment row multiplies by the number of skills, and your count explodes.

The safer and more explicit approach is to count distinct values on the right side of your aggregation boundary:

SELECT
    e.employee_id,
    e.full_name,
    e.department,
    COUNT(DISTINCT en.course_id) AS courses_enrolled,
    COUNT(DISTINCT CASE WHEN en.completed_at IS NOT NULL THEN en.course_id END) AS courses_completed
FROM employees e
LEFT JOIN enrollments en
    ON e.employee_id = en.employee_id
GROUP BY e.employee_id, e.full_name, e.department
ORDER BY courses_enrolled DESC;

A few things to unpack here:

LEFT JOIN instead of INNER JOIN: We switched to a LEFT JOIN so employees with zero enrollments still appear in the result (with a count of 0). An INNER JOIN would silently drop them.

COUNT(DISTINCT en.course_id): We count distinct course IDs rather than COUNT(*). This makes the query resilient to additional JOINs you might add later — if you JOIN to course_skills and a course has five skills, COUNT(DISTINCT en.course_id) still returns the right enrollment count.

COUNT(DISTINCT CASE WHEN ... END): The CASE expression inside COUNT filters which values get counted. We're counting distinct course IDs where a completion timestamp exists. This is a compact way to get multiple conditional aggregates in a single pass. See Combining Aggregates with Conditional Logic: GROUP BY, HAVING, and CASE WHEN in Practice for the full pattern.

---

Filtering on Both Sides of a Many-to-Many Relationship

One of the most common analytical tasks is filtering using properties from both sides. "Show me employees in the Engineering department who are enrolled in courses in the Leadership category." This sounds simple but has a subtle trap.

SELECT
    e.full_name,
    e.department,
    c.course_name,
    c.category
FROM employees e
INNER JOIN enrollments en
    ON e.employee_id = en.employee_id
INNER JOIN courses c
    ON en.course_id = c.course_id
WHERE e.department = 'Engineering'
  AND c.category = 'Leadership'
ORDER BY e.full_name;

That works for returning individual rows. But now consider: "How many Leadership courses is each Engineering employee enrolled in?" You want to include Engineering employees who have zero Leadership enrollments, so you can identify who needs them.

SELECT
    e.employee_id,
    e.full_name,
    COUNT(DISTINCT CASE WHEN c.category = 'Leadership' THEN en.course_id END) AS leadership_courses
FROM employees e
LEFT JOIN enrollments en
    ON e.employee_id = en.employee_id
LEFT JOIN courses c
    ON en.course_id = c.course_id
WHERE e.department = 'Engineering'
GROUP BY e.employee_id, e.full_name
HAVING COUNT(DISTINCT CASE WHEN c.category = 'Leadership' THEN en.course_id END) = 0
ORDER BY e.full_name;

Notice: we moved the category filter from WHERE to inside the CASE expression. If we kept WHERE c.category = 'Leadership', the LEFT JOIN would behave like an INNER JOIN for the filtering — employees with no Leadership courses would get filtered out entirely. By making the category filter conditional inside the aggregate, we keep all Engineering employees and simply count only their Leadership enrollments.

---

Finding Employees Who Have Completed a Specific Set of Courses

A trickier pattern: "Which employees have completed all of the courses in the Compliance category?" This is a relational division problem — you need to verify that an employee's completed courses are a superset of the required courses.

One clean approach: compare counts.

WITH compliance_courses AS (
    SELECT course_id
    FROM courses
    WHERE category = 'Compliance'
),
required_count AS (
    SELECT COUNT(*) AS total FROM compliance_courses
),
employee_completion AS (
    SELECT
        en.employee_id,
        COUNT(DISTINCT en.course_id) AS completed_compliance
    FROM enrollments en
    INNER JOIN compliance_courses cc
        ON en.course_id = cc.course_id
    WHERE en.completed_at IS NOT NULL
    GROUP BY en.employee_id
)
SELECT
    e.full_name,
    e.department,
    ec.completed_compliance,
    rc.total AS courses_required
FROM employee_completion ec
INNER JOIN employees e
    ON ec.employee_id = e.employee_id
CROSS JOIN required_count rc
WHERE ec.completed_compliance = rc.total
ORDER BY e.department, e.full_name;

Breaking down the CTEs:

• compliance_courses isolates the course IDs we care about
• required_count counts how many there are (this is the target number)
• employee_completion counts how many compliance courses each employee has completed
• The final query keeps only employees whose completed count matches the required total

The CROSS JOIN required_count adds the total to every row without grouping by it — a practical trick when you need a scalar reference value in a filter. For more CTE patterns like this, see Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture.

---

Aggregating From Both Sides Simultaneously

Sometimes you need stats about both sides of the relationship in the same query. For example: for each course, show the number of employees enrolled AND the number of skills it teaches.

The trap here is joining to both enrollments and course_skills in the same query before aggregating. The result set will have one row per (employee, course, skill) combination, so a course with 10 employees and 5 skills produces 50 rows. Counting employees gives 50, counting skills gives 50 — both wrong.

The naive broken version:

-- BROKEN - don't use this
SELECT
    c.course_id,
    c.course_name,
    COUNT(DISTINCT en.employee_id) AS enrolled_employees,  -- This works only because of DISTINCT
    COUNT(DISTINCT cs.skill_id) AS skill_count             -- This works only because of DISTINCT
FROM courses c
LEFT JOIN enrollments en ON c.course_id = en.course_id
LEFT JOIN course_skills cs ON c.course_id = cs.course_id
GROUP BY c.course_id, c.course_name;

Interestingly, using COUNT(DISTINCT ...) on both sides actually saves you here — each column is deduplicated independently. But this approach gets fragile as queries grow. The better approach: pre-aggregate each side separately, then join the aggregates.

WITH enrollment_counts AS (
    SELECT
        course_id,
        COUNT(DISTINCT employee_id) AS enrolled_employees,
        COUNT(DISTINCT CASE WHEN completed_at IS NOT NULL THEN employee_id END) AS completions
    FROM enrollments
    GROUP BY course_id
),
skill_counts AS (
    SELECT
        course_id,
        COUNT(DISTINCT skill_id) AS skill_count
    FROM course_skills
    GROUP BY course_id
)
SELECT
    c.course_id,
    c.course_name,
    c.category,
    c.credits,
    COALESCE(ec.enrolled_employees, 0) AS enrolled_employees,
    COALESCE(ec.completions, 0) AS completions,
    COALESCE(sc.skill_count, 0) AS skill_count,
    ROUND(
        COALESCE(ec.completions, 0) * 100.0 / NULLIF(ec.enrolled_employees, 0),
        1
    ) AS completion_rate_pct
FROM courses c
LEFT JOIN enrollment_counts ec ON c.course_id = ec.course_id
LEFT JOIN skill_counts sc ON c.course_id = sc.course_id
ORDER BY enrolled_employees DESC;

This is the production-grade pattern. Each CTE aggregates one side of the relationship completely before any joining happens. The final query joins three pre-aggregated results — courses, enrollment_counts, and skill_counts — with no row multiplication risk.

COALESCE(..., 0) handles courses that have no enrollments or no skills (which would produce NULLs from the LEFT JOINs). NULLIF(ec.enrolled_employees, 0) prevents division-by-zero in the completion rate. For more on handling NULLs in these situations, see NULL Handling in SQL: IS NULL, COALESCE, and NULLIF.

---

Finding Shared Connections: Which Employees Share Courses?

A powerful use of junction tables: finding entities on one side that share relationships with the same entity on the other side. "Which pairs of employees are enrolled in the same courses?"

This is a self-join through the junction table — join enrollments to itself on the course_id column.

SELECT
    e1.full_name AS employee_1,
    e2.full_name AS employee_2,
    COUNT(DISTINCT en1.course_id) AS shared_courses,
    STRING_AGG(DISTINCT c.course_name, ', ' ORDER BY c.course_name) AS course_names
FROM enrollments en1
INNER JOIN enrollments en2
    ON en1.course_id = en2.course_id
    AND en1.employee_id < en2.employee_id  -- prevents duplicates and self-pairs
INNER JOIN employees e1
    ON en1.employee_id = e1.employee_id
INNER JOIN employees e2
    ON en2.employee_id = e2.employee_id
INNER JOIN courses c
    ON en1.course_id = c.course_id
GROUP BY e1.employee_id, e1.full_name, e2.employee_id, e2.full_name
HAVING COUNT(DISTINCT en1.course_id) >= 2
ORDER BY shared_courses DESC, e1.full_name;

The crucial trick is en1.employee_id < en2.employee_id. Without it, every pair appears twice — (Alice, Bob) and (Bob, Alice) — and every employee appears paired with themselves. The less-than comparison ensures each pair appears exactly once, and the self-pairing (Alice, Alice) is impossible.

STRING_AGG (Postgres/SQL Server syntax; use GROUP_CONCAT in MySQL) concatenates the course names into a readable list. This is a great use of Multi-Table Reporting with JOIN and GROUP BY: Aggregating Across Relationships in a Single Query patterns.

---

Real-World Project: Building a Learning Progress Dashboard

Let's put everything together into a complete analytical report. The scenario: an HR analytics team needs a dashboard that shows, for each department:

1. Total employees
2. Employees with at least one enrollment
3. Total course completions
4. Most popular course category
5. Average courses per enrolled employee

WITH department_employees AS (
    SELECT
        department,
        COUNT(*) AS total_employees
    FROM employees
    GROUP BY department
),
department_enrollments AS (
    SELECT
        e.department,
        COUNT(DISTINCT e.employee_id) AS employees_with_enrollments,
        COUNT(DISTINCT en.enrollment_id) AS total_enrollments,
        COUNT(DISTINCT CASE WHEN en.completed_at IS NOT NULL THEN en.enrollment_id END) AS total_completions
    FROM employees e
    LEFT JOIN enrollments en
        ON e.employee_id = en.employee_id
    GROUP BY e.department
),
department_top_category AS (
    SELECT DISTINCT ON (e.department)
        e.department,
        c.category AS top_category,
        COUNT(*) AS category_enrollments
    FROM employees e
    INNER JOIN enrollments en ON e.employee_id = en.employee_id
    INNER JOIN courses c ON en.course_id = c.course_id
    GROUP BY e.department, c.category
    ORDER BY e.department, COUNT(*) DESC
)
SELECT
    de.department,
    de.total_employees,
    COALESCE(den.employees_with_enrollments, 0) AS employees_enrolled,
    ROUND(
        COALESCE(den.employees_with_enrollments, 0) * 100.0 / NULLIF(de.total_employees, 0),
        1
    ) AS enrollment_rate_pct,
    COALESCE(den.total_completions, 0) AS total_completions,
    COALESCE(dtc.top_category, 'N/A') AS top_course_category,
    ROUND(
        COALESCE(den.total_enrollments, 0) * 1.0 / NULLIF(den.employees_with_enrollments, 0),
        2
    ) AS avg_courses_per_enrolled_employee
FROM department_employees de
LEFT JOIN department_enrollments den
    ON de.department = den.department
LEFT JOIN department_top_category dtc
    ON de.department = dtc.department
ORDER BY enrollment_rate_pct DESC;

The structure here is deliberate: three CTEs, each handling one clean aggregation concern, followed by a final SELECT that joins only pre-aggregated data. No raw junction tables appear in the final join — everything has been collapsed to one row per department first.

---

Hands-On Exercise

Using the schema defined in this lesson, write the following queries. Use the sample data setup below to test your results.

-- Sample data
INSERT INTO employees VALUES
  (1, 'Amara Osei', 'Engineering', '2021-03-15'),
  (2, 'Jordan Patel', 'Engineering', '2020-07-01'),
  (3, 'Sofia Alvarez', 'Marketing', '2022-01-10'),
  (4, 'Marcus Lee', 'HR', '2019-11-20'),
  (5, 'Priya Nair', 'Marketing', '2023-02-28');

INSERT INTO courses VALUES
  (1, 'Data Fundamentals', 'Analytics', 3),
  (2, 'Leadership Essentials', 'Leadership', 2),
  (3, 'Workplace Safety', 'Compliance', 1),
  (4, 'Privacy & Data Law', 'Compliance', 2),
  (5, 'Advanced SQL', 'Analytics', 4);

INSERT INTO skills VALUES
  (1, 'Data Analysis', 'Intermediate'),
  (2, 'SQL', 'Intermediate'),
  (3, 'Team Management', 'Advanced'),
  (4, 'Regulatory Compliance', 'Foundational'),
  (5, 'Legal Literacy', 'Foundational');

INSERT INTO enrollments (enrollment_id, employee_id, course_id, enrolled_at, completed_at) VALUES
  (1, 1, 1, '2024-01-10', '2024-02-01'),
  (2, 1, 3, '2024-01-10', '2024-01-20'),
  (3, 1, 4, '2024-01-10', NULL),
  (4, 2, 1, '2024-02-01', '2024-03-01'),
  (5, 2, 5, '2024-02-15', NULL),
  (6, 3, 2, '2024-01-20', '2024-02-10'),
  (7, 3, 1, '2024-03-01', NULL),
  (8, 4, 3, '2023-12-01', '2023-12-15'),
  (9, 4, 4, '2023-12-01', '2024-01-05');

INSERT INTO course_skills VALUES
  (1, 1), (1, 2),
  (2, 3),
  (3, 4),
  (4, 4), (4, 5),
  (5, 1), (5, 2);

Exercise 1: Write a query that returns each employee's name and the number of skills they're exposed to through their enrolled courses. Count distinct skills, not courses.

Exercise 2: Find employees who are enrolled in at least one Analytics course but have not yet completed any of them. Return their name, department, and enrollment count.

Exercise 3: Which pairs of employees share at least one course enrollment? Return each pair once, with their shared course count and a comma-separated list of shared course names.

Exercise 4 (stretch): Write a query that identifies employees who have completed all Compliance courses. If there are no compliance courses, return an empty result set rather than erroring.

---

Common Mistakes & Troubleshooting

Count inflation from multiple junction table joins

Symptom: your COUNT(*) returns values that are clearly too large — like 30 when you expect 5.

Diagnosis: you've joined to two or more junction tables in the same query and aggregated without isolating the results. Every row from the first junction table multiplied by every row from the second.

Fix: pre-aggregate each junction table in its own CTE, then join the aggregated results. If you must keep the raw joins, use COUNT(DISTINCT primary_key_of_the_thing_you_want_to_count).

---

LEFT JOIN behaving like INNER JOIN

Symptom: employees with zero enrollments don't appear in results even though you used LEFT JOIN.

Diagnosis: you have a WHERE clause condition on a column from the right-side table. WHERE en.course_id IS NOT NULL or WHERE c.category = 'Leadership' will filter out all rows where the LEFT JOIN produced NULLs — effectively turning it back into an INNER JOIN.

Fix: move right-side filters into the JOIN's ON clause, or use conditional aggregation (COUNT(CASE WHEN c.category = 'Leadership' THEN 1 END)) instead of WHERE filtering.

---

Forgetting the UNIQUE constraint on junction tables

Symptom: an employee appears enrolled in the same course twice in reports, counts are doubled.

Diagnosis: the junction table lacks a unique constraint on (employee_id, course_id), so duplicate rows were inserted.

Fix: add UNIQUE(employee_id, course_id) or a composite primary key. For existing duplicate data, use COUNT(DISTINCT en.enrollment_id) as a workaround while you clean the data.

---

GROUP BY doesn't include all non-aggregated columns

Symptom: database error — "column must appear in GROUP BY or aggregate function."

Fix: every column in your SELECT that isn't inside an aggregate function (COUNT, SUM, MAX, etc.) must appear in the GROUP BY. If you're selecting employee_id, full_name, and department, all three belong in GROUP BY — even if employee_id functionally determines the others. Some databases let you get away with grouping by just the primary key; others are strict. When in doubt, include everything.

---

Performance: Missing indexes on junction table foreign keys

A junction table with millions of rows and no indexes on its foreign key columns will cause full table scans on every double-JOIN query. At minimum, index both foreign key columns — either individually or as a composite. In PostgreSQL and MySQL, a composite primary key (employee_id, course_id) creates an index on (employee_id, course_id) automatically. You may also want a separate index on (course_id, employee_id) to support lookups from the course side. See SQL Indexes Explained: How They Work and When to Create Them for the full indexing strategy.

---

Summary & Next Steps

Many-to-many relationships are a fundamental database pattern, and junction tables are how relational databases handle them. The core skills you've built in this lesson:

• Recognizing junction tables: they have foreign keys to two other tables, often a composite primary key or unique constraint, and may carry relationship metadata
• The double-JOIN pattern: chain your JOINs through the junction table to reach both sides
• Counting safely: use COUNT(DISTINCT ...) over raw COUNT(*) when other JOINs could inflate row counts; pre-aggregate in CTEs when joining multiple junction tables simultaneously
• Filtering across sides: use WHERE for hard filters on one side; use conditional aggregation (CASE WHEN inside COUNT) when you need counts that include zeros
• Self-joins through junction tables: find shared connections by joining a junction table to itself, using < to prevent duplicate pairs

The patterns here — CTE-based pre-aggregation, COUNT(DISTINCT), conditional aggregation — are the same ones you'll use in analytical SQL work across industries. If you want to push further, explore window functions for ranking across many-to-many results in Window Functions: RANK, ROW_NUMBER, and LAG, or apply these junction table patterns to cohort analysis in SQL for Data Analysis: Cohort Analysis, Funnels, and Retention - Complete Guide.