A fairly difficult subject to approach in MySQL is Many to Many relationships. The queries can be complicated and the data sets huge, but as it seems to be covered by tutorials very little I thought I’d have a shot a writing a tutorial about it.

Many to many relationships are something you will at some point encounter if you start to work with complex data sets in MySQL. In essence they are just sets of data that can relate to multiple items on both sides. In this tutorial I’m going to go back to the films database we used at in the previous MySQL tutorial. For reference let’s look at what our tables look like:

First the Celebrities table:

Next the Films table:

Finally there is the int_cf table, but as we’ll be looking at it in more detail in a few minutes I’ll wait to show you the contents.

What Are Many To Many Relationships?

Many to many relationships are data sets that have multiple relations on both sides. Let’s use our films database as an example. We have a table of Celebrities, and a table of Films. We can have many Celebrities in a film, but we can also have many films that one Celebrity has starred in. This means there is a one to many relationship between both tables, which equals a many to many relationship.

Now let’s take a look at an example. Please remember this is just a look at how many to many relationships work & how to query data from them, I can’t promise that my tables are correctly normalized.

What Do They Look Like?

Many to many relationships are generally (although not always) joined via another table. This table holds foreign keys. Foreign keys are primary keys from another table, these are used to determine which rows in one table are related to which rows in a second table.

Okay so I promised you earlier that we’d look at the contents of the int_cf table, well here we go:

As you can see it’s just a list of numbers, but these numbers (foreign keys) are going to help us get some meaningful information. Let’s take a look at a MySQL query to help us use this table to get some useful information.

SELECT c.name, cf.title
FROM celebrities AS c
JOIN (
	SELECT icf.c_id, icf.f_id, f.title
	FROM int_cf AS icf
	JOIN films AS f
	ON icf.f_id = f.f_id
) AS cf
ON c.c_id = cf.c_id
ORDER BY c.c_id ASC

If you read the last tutorial on sub queries you will be familiar with this type of query, if not I’d suggest going back and reading all about sub queries and how they are useful.

Now, let’s go through how this uses the joining table (named int_cf) to join the Celebrities & Films tables together. Let’s first look at the sub query.

SELECT icf.c_id, icf.f_id, f.title
FROM int_cf AS icf
JOIN films AS f
ON icf.f_id = f.f_id

This takes int_cf and joins the Films table to it, this is the first part of joining the information. By joining int_cf to our films table wherever the keys (f_id) match we can now see which films are associated with which key. This query gives us a result like this:

As you can see our query has joined the correct films onto the keys in the int_cf table.

Now let’s take a look at what the main query would do without the sub query. First a look at the query:

SELECT c.name, icf.c_id, icf.f_id
FROM celebrities AS c
JOIN int_cf AS icf
ON c.c_id = icf.c_id
ORDER BY c.c_id ASC

The query has been slightly restructured to stop it from erroring because of the sub query being missing, but it does give the equivalent result. Here is what it give:

As you can see it is the opposite side of the data we need. It has the Celebrities joined to their corresponding keys just like the previous query joined the Films to their corresponding keys. Now hopefully you can guess what the combined query does? That’s right it glues these two parts together into one very useful result set.

Let’s take one last look at the query as a whole:

SELECT c.name, cf.title
FROM celebrities AS c
JOIN (
	SELECT icf.c_id, icf.f_id, f.title
	FROM int_cf AS icf
	JOIN films AS f
	ON icf.f_id = f.f_id
) AS cf
ON c.c_id = cf.c_id
ORDER BY c.c_id ASC

Now let’s take a look at the results this gives you:

The query wouldn’t actually have the two columns of keys included in the result, as I’ve shown in the table, but I’ve added them for reference which will hopefully help you see how the two tables have been joined together via their foreign keys that are held inside the int_cf table.

A small thing that you may be thinking is why is a film mentioned twice? Well two people have been in it. Why is a celebrity mentioned twice? Well they’ve been in two films. I know it seems like it doesn’t need explaining, but you’d be amazed the amount of questions I’ll get if I don’t make that clear. You would deal with how to display information like that in your application coding.

Many To Many Relationships: In Summary

Many to many relationships have, as far as I’m concerned, always been a complicated part of working with DBMS (Database Management Systems) and while it is difficult you do get more accustomed to using them as time goes on. Please bare in mind I’m no expert, and this tutorial is just a result of having worked with them for a while. I’m hoping my experiences will help those who are just starting out.

So that’s it. As always if you have any questions, problems or anything useful to add please drop it in the comments. Next time we are going to look into how to take that basic many to many query & use it to query data on a specific Celebrity or Film instead of just returning them all as it’s something I’ve received a lot of email on.