MySQL Many to Many Relationships

In MySQL Joins article, we looked at one-to-one and one-to-many relationships.

In this article we are going to look at many-to-many relationships which have a special storing mechanism compared to other relationships.

That is, there is an intermediate table to store relationships when two tables can have many-to-many relationships.

Consider `employee` and `education` tables listed below (SQL commands for creating the tables and inserting data are available here).

Think that Robin and Taylor hold BSc while Vivian holds both MSc and PhD. These relationships can be graphically represented like below.

You can see that for Vivian in `employee` table there are two related rows in `education` table and for BSc in `education` table there are two related rows in `employee` table forming many-to-many relationships (Each row in `employee` table can have more that one related row in `education` table and vise versa).

For storing many-to-many relationships, we need an intermediate table that mainly stores the primary keys (IDs) of each relationship. In this case, we can use a table (`employee_education`) like below.

Once relationships are stored, you can fetch data like below. In this query we fetch employee names and their education levels using SELECT statements and left joins.

SELECT emp.first_name, emp.last_name, edu.name AS edu_name FROM `employee` AS emp LEFT JOIN `employee_education` AS ee ON emp.id = ee.employee_id LEFT JOIN `education` AS edu ON ee.education_id = edu.id;

+------------+-----------+----------+
| first_name | last_name | edu_name |
+------------+-----------+----------+
| Robin      | Jackman   | BSc      |
| Taylor     | Edward    | BSc      |
| Vivian     | Dickens   | MSc      |
| Vivian     | Dickens   | PhD      |
| Harry      | Clifford  | NULL     |
| Eliza      | Clifford  | NULL     |
| Nancy      | Newman    | NULL     |
| Melinda    | Clifford  | NULL     |
| Harley     | Gilbert   | NULL     |
+------------+-----------+----------+

You can omit the employees whose education levels are not set by having a WHERE clause.

SELECT emp.first_name, emp.last_name, edu.name AS edu_name FROM `employee` AS emp LEFT JOIN `employee_education` AS ee ON emp.id = ee.employee_id LEFT JOIN `education` AS edu ON ee.education_id = edu.id WHERE edu.name IS NOT NULL;

+------------+-----------+----------+
| first_name | last_name | edu_name |
+------------+-----------+----------+
| Robin      | Jackman   | BSc      |
| Taylor     | Edward    | BSc      |
| Vivian     | Dickens   | MSc      |
| Vivian     | Dickens   | PhD      |
+------------+-----------+----------+