Many-to-many with join tables
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A student takes many courses and a course has many students. A many-to-many relationship like this cannot be stored with one foreign key; it needs a third table with one row for each pair.
Why many-to-many needs a third table
A many-to-many (M:N) relationship is one where a row on each side can link to many rows on the other side, such as students and courses. A foreign key holds one value, so it needs a third table: a join table.
A student takes many courses, so where does the foreign key go? Try the obvious design: with course_id in students, Aditya must be copied once for every extra course.
CREATE TABLE students_v1 ( roll_no VARCHAR(10) NOT NULL, name VARCHAR(40) NOT NULL, course_id INT NOT NULL -- room for ONE course only);INSERT INTO students_v1 VALUES ('CS01', 'Aditya', 1), ('CS01', 'Aditya', 2), -- Aditya copied for a 2nd course ('CS02', 'Bhavana', 1);
SELECT roll_no, name, COUNT(*) AS copiesFROM students_v1GROUP BY roll_no, nameORDER BY roll_no;+---------+---------+--------+ | roll_no | name | copies | +---------+---------+--------+ | CS01 | Aditya | 2 | | CS02 | Bhavana | 1 | +---------+---------+--------+
A foreign key holds one value per row, so it cannot sit on either side. Put course_id in students and a student can take only one course, unless you copy the whole student row for each extra course. Put student_id in courses and the same happens to courses. A comma list such as '1,2,4' in a cell has all the problems of the last lesson.
The join table and its composite key
A join table (also called a junction, bridge or link table) stores one row for each linked pair. It has two foreign keys, one to each side, and its primary key is usually the pair itself (a composite key).
A join table turns one many-to-many line into two one-to-many lines. The diagram shows students, enrollments and courses; the script builds a join table for films and actors.
Example 1 · ER diagram: students, enrollments and courses
+----------------+ +------------------+ +---------------+| students | | enrollments | | courses ||----------------| |------------------| |---------------|| PK student_id | ||------o{ | PK,FK student_id | o{------|| | PK course_id || name | | PK,FK course_id | | code (UQ) || branch | | marks | | title |+----------------+ +------------------+ +---------------+One student has zero or many enrollments; each enrollment is for exactly one student. One course has zero or many enrollments; each enrollment is for exactly one course. Together: a student takes many courses and a course has many students (M:N).
What this code does
- The M:N line between students and courses is drawn as two 1:N lines meeting at
enrollments. Both foreign keys are on the many side, the join table. PK,FKon both columns: each is a foreign key, and together they form the composite primary key.markssits in the join table because it belongs to the pair.
Example 2 · A join table for films and actors
CREATE TABLE films ( film_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(60) NOT NULL, year SMALLINT NOT NULL);
CREATE TABLE actors ( actor_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(60) NOT NULL);
CREATE TABLE film_cast ( film_id INT NOT NULL, actor_id INT NOT NULL, role VARCHAR(60) NOT NULL, -- a fact about the pair PRIMARY KEY (film_id, actor_id), -- composite key: each pair once FOREIGN KEY (film_id) REFERENCES films (film_id), FOREIGN KEY (actor_id) REFERENCES actors (actor_id));
INSERT INTO films (title, year) VALUES ('3 Idiots', 2009), ('Dangal', 2016), ('Rang De Basanti', 2006);INSERT INTO actors (name) VALUES ('Aamir Khan'), ('R. Madhavan'), ('Fatima Sana Shaikh');INSERT INTO film_cast VALUES (1, 1, 'Rancho'), (1, 2, 'Farhan'), (2, 1, 'Mahavir Singh Phogat'), (2, 3, 'Geeta Phogat'), (3, 1, 'DJ'), (3, 2, 'Ajay Rathod');
-- Films of one actorSELECT f.title, f.year, fc.roleFROM actors aJOIN film_cast fc ON fc.actor_id = a.actor_idJOIN films f ON f.film_id = fc.film_idWHERE a.name = 'R. Madhavan'ORDER BY f.year;
-- The same pair cannot be added twiceINSERT INTO film_cast VALUES (1, 2, 'Farhan Qureshi');+-----------------+------+-------------+ | title | year | role | +-----------------+------+-------------+ | Rang De Basanti | 2006 | Ajay Rathod | | 3 Idiots | 2009 | Farhan | +-----------------+------+-------------+ ERROR 1062 (23000): Duplicate entry '1-2' for key 'film_cast.PRIMARY'
What this code does
- Aamir Khan is in three films and 3 Idiots has two actors: both sides are "many", and each link is one small row in
film_cast. - Reaching films from an actor takes two joins: actor → film_cast → film.
roleis stored infilm_castbecause it describes this actor in this film. - The composite key rejects a second (1, 2) row with error 1062. MySQL shows the duplicate pair as
1-2.
In enrollments, the primary key is the pair itself, PRIMARY KEY (student_id, course_id). A primary key made of two or more columns is a composite key. It means "this student in this course" can be stored only once, so a double click on "Enroll" cannot create two rows.
Fix the error
Fix the join table key
The second insert fails. Pick the line that fixes the key.
Round 1 of 2
A student cannot be enrolled in a second course. Which key line fixes enrollments?
Error
ERROR 1062 (23000): Duplicate entry '1' for key 'enrollments.PRIMARY'
enrollments, film_cast, order_items. Names like students_courses work, but a real name also tells you where extra columns belong.Extra columns on the relationship
An attribute of the relationship is a fact that belongs to the pair, not to either side, such as marks, quantity or unit_price. It is stored as a column of the join table.
Some facts belong to the pair, not to either side. The two scripts below keep a student's marks and an order line's price as columns of the join table.
Example 3 · Marks belong to the pair
SELECT s.name, c.code, c.title, e.marksFROM enrollments eJOIN students s ON s.student_id = e.student_idJOIN courses c ON c.course_id = e.course_idWHERE s.name IN ('Aditya Joshi', 'Eshan Gupta')ORDER BY s.name, c.code;+--------------+-------+-------------------------+-------+ | name | code | title | marks | +--------------+-------+-------------------------+-------+ | Aditya Joshi | CS201 | Data Structures | 88 | | Aditya Joshi | CS202 | Database Systems | 92 | | Aditya Joshi | MA101 | Engineering Mathematics | 75 | | Eshan Gupta | HS101 | Communication Skills | NULL | | Eshan Gupta | MA101 | Engineering Mathematics | 45 | +--------------+-------+-------------------------+-------+
What this code does
- Aditya has three marks and each course has many students' marks, so
marksfits in neitherstudentsnorcourses. It lives inenrollments, one value per pair. - Eshan's HS101 marks are
NULL: he is enrolled, but the exam has not been graded. The pair exists even when its extra column is still empty.
Example 4 · Why order_items keeps its own unit_price
-- Festival sale: the Redmi Note 13 drops by Rs 1,000UPDATE products SET price = 16999.00 WHERE product_id = 1;
SELECT oi.order_id, p.name, oi.unit_price AS price_paid, p.price AS price_today, oi.quantity * oi.unit_price AS line_totalFROM order_items oiJOIN products p ON p.product_id = oi.product_idWHERE oi.product_id = 1ORDER BY oi.order_id;+----------+---------------+------------+-------------+------------+ | order_id | name | price_paid | price_today | line_total | +----------+---------------+------------+-------------+------------+ | 1 | Redmi Note 13 | 17999.00 | 16999.00 | 17999.00 | | 11 | Redmi Note 13 | 17999.00 | 16999.00 | 17999.00 | +----------+---------------+------------+-------------+------------+
What this code does
- After the price change,
products.priceshows today's price, but each order still shows what the customer really paid. The bills of orders 1 and 11 do not change. quantityandunit_priceare facts about "this product in this order", so they belong in the join table. Copying the price here is deliberate, not a duplicate.
ShopLite's order_items is the best example. quantity is how many of this product are in this order. unit_price is the price at the time of the order. Why copy the price when products.price already has it? Because prices change. If an order read today's price, every old bill would change whenever the shop ran a sale.
When the same pair can happen more than once, such as a patient visiting the same doctor every month, the pair alone is no longer unique. Then the key must include what makes each row different (visit_date), or the table gets its own id.
Students and courses, orders and products
Querying through a join table takes two joins: one side to the join table, then the join table to the other side. The queries below go through enrollments in both directions and count units sold per product.
Example 5 · Through the join table in both directions
-- Courses per student, including students with noneSELECT s.name, COUNT(e.course_id) AS coursesFROM students sLEFT JOIN enrollments e ON e.student_id = s.student_idGROUP BY s.student_id, s.nameORDER BY courses DESC, s.name;
-- Students per course, with the average of graded marksSELECT c.code, c.title, COUNT(*) AS students, ROUND(AVG(e.marks), 1) AS avg_marksFROM courses cJOIN enrollments e ON e.course_id = c.course_idGROUP BY c.course_id, c.code, c.titleORDER BY c.code;+-----------------+---------+ | name | courses | +-----------------+---------+ | Aditya Joshi | 3 | | Deepika Nair | 3 | | Bhavana Reddy | 2 | | Chirag Patel | 2 | | Eshan Gupta | 2 | | Fathima Begum | 2 | | Gaurav Mishra | 2 | | Harini Krishnan | 0 | +-----------------+---------+ +-------+-------------------------+----------+-----------+ | code | title | students | avg_marks | +-------+-------------------------+----------+-----------+ | CS201 | Data Structures | 4 | 81.5 | | CS202 | Database Systems | 4 | 76.5 | | EC301 | Digital Electronics | 2 | 69.5 | | HS101 | Communication Skills | 2 | 80.0 | | MA101 | Engineering Mathematics | 4 | 64.8 | +-------+-------------------------+----------+-----------+
What this code does
- Grouping by student counts courses; grouping by course counts students. One join table answers both questions.
- The
LEFT JOINkeeps Harini Krishnan, who has no enrollments yet, with a count of 0.AVG(e.marks)skips the ungradedNULL, so HS101's average is just Deepika's 80.
Example 6 · ShopLite: units sold per product
-- A new accessory arrives; nobody has ordered it yetINSERT INTO products (name, category_id, price, stock)VALUES ('USB-C Cable', 4, 349.00, 100);
SELECT p.name, COUNT(oi.order_id) AS orders, COALESCE(SUM(oi.quantity), 0) AS unitsFROM products pLEFT JOIN order_items oi ON oi.product_id = p.product_idWHERE p.category_id = 4GROUP BY p.product_id, p.nameORDER BY units DESC, p.name;+------------------------+--------+-------+ | name | orders | units | +------------------------+--------+-------+ | boAt Airdopes 141 | 4 | 7 | | Mi Power Bank 20000mAh | 2 | 3 | | Logitech M331 Mouse | 1 | 1 | | USB-C Cable | 0 | 0 | +------------------------+--------+-------+
What this code does
ordersandorder_itemsare the same many-to-many pattern: an order has many products and a product is in many orders.order_itemsis the join table, with(order_id, product_id)as its composite key.- Grouping by product counts the orders and adds the quantities. The
LEFT JOINkeeps the new USB-C Cable, which has no order lines.COUNT(oi.order_id)gives 0 andCOALESCEturns itsNULLsum into 0.
The same join table answers questions in both directions: "which courses does Aditya take?" and "who takes Database Systems?". Use LEFT JOIN when rows with no pairs must also appear, such as a student who has not enrolled yet or a product nobody has bought.
Next step
Check your understanding
11 quick questions