Categories

Articles in MySQL Category

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.

Many-to-many relationships

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      |
+------------+-----------+----------+
Where to Head from Here...
Share with Your Peers...

We Value Your Feedback...

We love to hear what you think about this article. Please provide your opinion, suggestions and improvements using following form. Note that submitted feedback is not displayed but we will get back to you if it needs a reply.