{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"many-to-many-bridge-tables","title":"Many-to-many, and the table in the middle","url":"https://addaly.com/learn/data-and-sql/many-to-many-bridge-tables","position":16,"of":66,"minutes":8,"answer":"A many-to-many relationship needs a table in the middle, and when you count across it a LEFT JOIN's unmatched row still counts as a row unless you count the child's column rather than *.","keyIdea":"A many-to-many relationship needs a table in the middle, and when you count across it a LEFT JOIN's unmatched row still counts as a row unless you count the child's column rather than `*`."},"body":{"format":"markdown","text":"## The relationship that needs its own table\n\nA student takes several courses. A course has many students. There is no column you can add to either table that holds this, because a column holds one value and you need many.\n\nThe answer is a third table whose rows *are* the relationship:\n\n```sql\nCREATE TABLE enrolments (\n  student_id  bigint NOT NULL REFERENCES students(id),\n  course_id   bigint NOT NULL REFERENCES courses(id),\n  enrolled_on date   NOT NULL DEFAULT current_date,\n  status      text   NOT NULL DEFAULT 'active',\n  PRIMARY KEY (student_id, course_id)\n);\n```\n\nThis is variously called a bridge, junction, join or associative table. The name does not matter. What matters is that it is a real table with a real grain (one row per student per course) and it can carry its own columns, which is the part people underuse. The date somebody enrolled, whether they dropped out, what they paid: all of these are facts about the *relationship*, not about the student or the course, and this is the only correct place for them.\n\n## Querying across it\n\nTwo joins, in order:\n\n```sql\nSELECT s.full_name, c.title, e.enrolled_on\nFROM students s\nJOIN enrolments e ON e.student_id = s.id\nJOIN courses    c ON c.id = e.course_id\nWHERE c.title = 'Statistics I';\n```\n\nGrain check: the result is one row per enrolment, which is one row per student per course. A student on three courses appears three times. That is correct and expected, and it is exactly the multiplication that ruins any per-student total you compute on top of it without grouping first.\n\n## Signs the bridge table is missing\n\nYou will meet data where somebody avoided the third table. Two symptoms:\n\n**A comma-separated column.** `students.courses` holding `'stats,algebra,physics'`. Every question becomes a string search. `WHERE courses LIKE '%stats%'` also matches a course called `'biostats'`. Counting how many students take algebra requires counting commas. Adding a course means rewriting a string in every affected row, transactionally, without race conditions, which nobody does correctly.\n\n**Numbered columns.** `course1, course2, course3`. The moment somebody takes a fourth, you are altering the schema, and every query has to be rewritten to check three columns instead of two. Ask \"how many students take Statistics I\" and you need `WHERE course1 = 'stats' OR course2 = 'stats' OR course3 = 'stats'`.\n\nBoth are the \"no repeating groups\" rule from the first module, wearing different clothes. The fix in both cases is to unpack them into a bridge table once and never think about it again.\n\n## Counting on both sides\n\nBecause the bridge table holds the relationship, both directions are the same shape of query:\n\n```sql\n-- students per course\nSELECT c.title, count(*) AS students\nFROM courses c JOIN enrolments e ON e.course_id = c.id\nGROUP BY c.title;\n\n-- courses per student\nSELECT s.full_name, count(*) AS courses\nFROM students s JOIN enrolments e ON e.student_id = s.id\nGROUP BY s.full_name;\n```\n\nNote that both of these silently exclude students with no courses and courses with no students, because an inner join drops unmatched rows. If the report is about coverage, which courses nobody signed up for, you need a LEFT JOIN from the side you care about and `count(e.course_id)` rather than `count(*)`, since `count(*)` would count the single NULL-filled row as one.\n\nThat distinction, `count(*)` versus `count(column)` after a left join, is the most common way an \"empty\" category is reported as having one member.\n\n## Two useful patterns\n\n**\"Students taking both A and B\"**: a question that trips people up because no single row contains both:\n\n```sql\nSELECT student_id\nFROM enrolments\nWHERE course_id IN (11, 22)\nGROUP BY student_id\nHAVING count(DISTINCT course_id) = 2;\n```\n\nGroup and count, rather than trying to join the table to itself. This generalises: change the 2 and the list and it answers \"all of these\", for any number.\n\n**\"Courses with no students\"**: the anti-join:\n\n```sql\nSELECT c.* FROM courses c\nLEFT JOIN enrolments e ON e.course_id = c.id\nWHERE e.course_id IS NULL;\n```\n\n## One design note\n\nWhether the bridge table gets its own surrogate id, in addition to the composite primary key, is a real argument with no universal answer. A composite key states the rule that the pair is unique and is self-documenting. A surrogate id makes the table easier to reference from a fourth table and easier for some application frameworks to handle. Pick one, and if you add a surrogate id, keep a UNIQUE constraint on the pair: otherwise the rule you were relying on is gone and duplicate enrolments will appear."},"check":{"question":"`SELECT c.title, count(*) FROM courses c LEFT JOIN enrolments e ON e.course_id = c.id GROUP BY c.title` shows 1 for a course you know has no students. Why?","options":[{"text":"The LEFT JOIN produced a separate row for the course itself, and `count(*)` includes that course row alongside its enrolment rows, adding one to every count.","correct":false,"tempting":"Close to the mechanism but misdescribes it: there is only ever one output row per course-enrolment pair, not a separate row for the course."},{"text":"The unmatched course still yields one output row with NULL enrolment columns, and `count(*)` counts rows whether or not those columns are NULL.","correct":true},{"text":"`count(*)` cannot be used with GROUP BY on a left-joined table and should be `count(DISTINCT *)`.","correct":false,"tempting":"`count(DISTINCT *)` is not valid SQL, and distinctness is not the issue: there is genuinely one row."},{"text":"The courses table contains a duplicate row for each course with no enrolments.","correct":false,"tempting":"Attributes the off-by-one to bad source data rather than to the join, which would also make the counts wrong for popular courses."}]},"prev":{"slug":"join-keys-that-do-not-match","title":"When the join key looks right and matches nothing","url":"https://addaly.com/learn/data-and-sql/join-keys-that-do-not-match"},"next":{"slug":"the-fan-trap","title":"Two child tables, one very wrong total","url":"https://addaly.com/learn/data-and-sql/the-fan-trap"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Many-to-many, and the table in the middle', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/many-to-many-bridge-tables (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Many-to-many, and the table in the middle","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":2,"slug":"putting-tables-together","title":"Putting tables together"},"answer":null,"keyIdea":"A many-to-many relationship needs a table in the middle, and when you count across it a LEFT JOIN’s unmatched row still counts as a row unless you count the child’s column rather than `*`.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"the-relationship-that-needs-its-own-table","runs":[{"text":"The relationship that needs its own table"}]},{"t":"p","runs":[{"text":"A student takes several courses. A course has many students. There is no column you can add to either table that holds this, because a column holds one value and you need many."}]},{"t":"p","runs":[{"text":"The answer is a third table whose rows "},{"text":"are","i":true},{"text":" the relationship:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE TABLE enrolments (\n  student_id  bigint NOT NULL REFERENCES students(id),\n  course_id   bigint NOT NULL REFERENCES courses(id),\n  enrolled_on date   NOT NULL DEFAULT current_date,\n  status      text   NOT NULL DEFAULT 'active',\n  PRIMARY KEY (student_id, course_id)\n);"},{"t":"p","runs":[{"text":"This is variously called a bridge, junction, join or associative table. The name does not matter. What matters is that it is a real table with a real grain (one row per student per course) and it can carry its own columns, which is the part people underuse. The date somebody enrolled, whether they dropped out, what they paid: all of these are facts about the "},{"text":"relationship","i":true},{"text":", not about the student or the course, and this is the only correct place for them."}]},{"t":"h2","id":"querying-across-it","runs":[{"text":"Querying across it"}]},{"t":"p","runs":[{"text":"Two joins, in order:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT s.full_name, c.title, e.enrolled_on\nFROM students s\nJOIN enrolments e ON e.student_id = s.id\nJOIN courses    c ON c.id = e.course_id\nWHERE c.title = 'Statistics I';"},{"t":"p","runs":[{"text":"Grain check: the result is one row per enrolment, which is one row per student per course. A student on three courses appears three times. That is correct and expected, and it is exactly the multiplication that ruins any per-student total you compute on top of it without grouping first."}]},{"t":"h2","id":"signs-the-bridge-table-is-missing","runs":[{"text":"Signs the bridge table is missing"}]},{"t":"p","runs":[{"text":"You will meet data where somebody avoided the third table. Two symptoms:"}]},{"t":"p","runs":[{"text":"A comma-separated column.","b":true},{"text":" "},{"text":"students.courses","code":true},{"text":" holding "},{"text":"'stats,algebra,physics'","code":true},{"text":". Every question becomes a string search. "},{"text":"WHERE courses LIKE '%stats%'","code":true},{"text":" also matches a course called "},{"text":"'biostats'","code":true},{"text":". Counting how many students take algebra requires counting commas. Adding a course means rewriting a string in every affected row, transactionally, without race conditions, which nobody does correctly."}]},{"t":"p","runs":[{"text":"Numbered columns.","b":true},{"text":" "},{"text":"course1, course2, course3","code":true},{"text":". The moment somebody takes a fourth, you are altering the schema, and every query has to be rewritten to check three columns instead of two. Ask “how many students take Statistics I” and you need "},{"text":"WHERE course1 = 'stats' OR course2 = 'stats' OR course3 = 'stats'","code":true},{"text":"."}]},{"t":"p","runs":[{"text":"Both are the “no repeating groups” rule from the first module, wearing different clothes. The fix in both cases is to unpack them into a bridge table once and never think about it again."}]},{"t":"h2","id":"counting-on-both-sides","runs":[{"text":"Counting on both sides"}]},{"t":"p","runs":[{"text":"Because the bridge table holds the relationship, both directions are the same shape of query:"}]},{"t":"code","lang":"sql","label":"SQL","text":"-- students per course\nSELECT c.title, count(*) AS students\nFROM courses c JOIN enrolments e ON e.course_id = c.id\nGROUP BY c.title;\n\n-- courses per student\nSELECT s.full_name, count(*) AS courses\nFROM students s JOIN enrolments e ON e.student_id = s.id\nGROUP BY s.full_name;"},{"t":"p","runs":[{"text":"Note that both of these silently exclude students with no courses and courses with no students, because an inner join drops unmatched rows. If the report is about coverage, which courses nobody signed up for, you need a LEFT JOIN from the side you care about and "},{"text":"count(e.course_id)","code":true},{"text":" rather than "},{"text":"count(*)","code":true},{"text":", since "},{"text":"count(*)","code":true},{"text":" would count the single NULL-filled row as one."}]},{"t":"p","runs":[{"text":"That distinction, "},{"text":"count(*)","code":true},{"text":" versus "},{"text":"count(column)","code":true},{"text":" after a left join, is the most common way an “empty” category is reported as having one member."}]},{"t":"h2","id":"two-useful-patterns","runs":[{"text":"Two useful patterns"}]},{"t":"p","runs":[{"text":"”Students taking both A and B”","b":true},{"text":": a question that trips people up because no single row contains both:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT student_id\nFROM enrolments\nWHERE course_id IN (11, 22)\nGROUP BY student_id\nHAVING count(DISTINCT course_id) = 2;"},{"t":"p","runs":[{"text":"Group and count, rather than trying to join the table to itself. This generalises: change the 2 and the list and it answers “all of these”, for any number."}]},{"t":"p","runs":[{"text":"”Courses with no students”","b":true},{"text":": the anti-join:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT c.* FROM courses c\nLEFT JOIN enrolments e ON e.course_id = c.id\nWHERE e.course_id IS NULL;"},{"t":"h2","id":"one-design-note","runs":[{"text":"One design note"}]},{"t":"p","runs":[{"text":"Whether the bridge table gets its own surrogate id, in addition to the composite primary key, is a real argument with no universal answer. A composite key states the rule that the pair is unique and is self-documenting. A surrogate id makes the table easier to reference from a fourth table and easier for some application frameworks to handle. Pick one, and if you add a surrogate id, keep a UNIQUE constraint on the pair: otherwise the rule you were relying on is gone and duplicate enrolments will appear."}]}],"check":{"question":[{"text":"SELECT c.title, count(*) FROM courses c LEFT JOIN enrolments e ON e.course_id = c.id GROUP BY c.title","code":true},{"text":" shows 1 for a course you know has no students. Why?"}],"options":[{"text":[{"text":"The LEFT JOIN produced a separate row for the course itself, and "},{"text":"count(*)","code":true},{"text":" includes that course row alongside its enrolment rows, adding one to every count."}],"correct":false,"tempting":[{"text":"Close to the mechanism but misdescribes it: there is only ever one output row per course-enrolment pair, not a separate row for the course."}]},{"text":[{"text":"The unmatched course still yields one output row with NULL enrolment columns, and "},{"text":"count(*)","code":true},{"text":" counts rows whether or not those columns are NULL."}],"correct":true,"tempting":null},{"text":[{"text":"count(*)","code":true},{"text":" cannot be used with GROUP BY on a left-joined table and should be "},{"text":"count(DISTINCT *)","code":true},{"text":"."}],"correct":false,"tempting":[{"text":"count(DISTINCT *)","code":true},{"text":" is not valid SQL, and distinctness is not the issue: there is genuinely one row."}]},{"text":[{"text":"The courses table contains a duplicate row for each course with no enrolments."}],"correct":false,"tempting":[{"text":"Attributes the off-by-one to bad source data rather than to the join, which would also make the counts wrong for popular courses."}]}],"order":[2,0,3,1],"why":null},"prev":{"slug":"join-keys-that-do-not-match","title":"When the join key looks right and matches nothing","minutes":8},"next":{"slug":"the-fan-trap","title":"Two child tables, one very wrong total","minutes":9}}}