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).
id | first_name | last_name | job_title | salary | notes |
---|---|---|---|---|---|
1 | Robin | Jackman | Software Engineer | 5500 | |
2 | Taylor | Edward | Software Architect | 7200 | |
3 | Vivian | Dickens | Database Administrator | 6000 | |
4 | Harry | Clifford | Database Administrator | 6800 | |
5 | Eliza | Clifford | Software Engineer | 4750 | |
6 | Nancy | Newman | Software Engineer | 5100 | |
7 | Melinda | Clifford | Project Manager | 8500 | |
8 | Harley | Gilbert | Software Architect | 8000 |
id | name |
---|---|
1 | BSc |
2 | MSc |
3 | PhD |
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.
employee_id | education_id |
---|---|
1 | 1 |
2 | 1 |
3 | 2 |
3 | 3 |
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 | +------------+-----------+----------+