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 | +------------+-----------+----------+