MySQL: Many To Many Relationships

/ Misc / by Paul Robinson / 7 Comments
This post was published back on February 8, 2011 and may be outdated. Please use caution when following older tutorials or using older code. After reading be sure to check for newer procedures or updates to code.

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:

id name
1 Brendan Fraser
2 Rachel Weisz
3 Jennifer Morrison
4 Yvonne Strahovski

Next the Films table:

id name
1 The Mummy
2 The Mummy Returns
3 Star Trek
4 The Canyon

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:

c_id f_id
1 1
1 2
2 1
2 2
3 3
4 4

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.

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.

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:

c_id f_id title
1 1 The Mummy
1 2 The Mummy Returns
2 1 The Mummy
2 2 The Mummy Returns
3 3 Star Trek
4 4 The Canyon

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:

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:

name c_id f_id
Brendan Fraser 1 1
Brendan Fraser 1 2
Rachel Weisz 2 1
Rachel Weisz 2 2
Jennifer Morrison 3 3
Yvonne Strahovski 4 4

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:

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

name c_id f_id title
Brendan Fraser 1 1 The Mummy
Brendan Fraser 1 2 The Mummy Returns
Rachel Weisz 2 1 The Mummy
Rachel Weisz 2 2 The Mummy Returns
Jennifer Morrison 3 3 Star Trek
Yvonne Strahovski 4 4 The Canyon

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. 🙂


Author’s gravatar

How do you display the results of this query so they can be displayed in a table as follows(the movies populate the cell of the actor row) : great tutorial by the way!

name title
Brendan Fraser The Mummy,The Mummy Returns
Rachel Weisz The Mummy, The Mummy Returns
Jennifer Morrison Star Trek
Yvonne Strahovski The Canyon

Author’s gravatar author

Hi Arob,

Do you mean how would you display all the movies belonging to one actor/ress in one row, rather than repeating them?

I’d never given it much thought, but I don’t think it would be possible via MySQL. My best guess would be you’d create a function via PHP to do it. It would be fairly simple provided you have a basic knowledge of PHP.

It’s a little too large for the comments so drop me and email if you need further help. The basic idea though would be to store the actor/ress name & check to see if it is identical in the next loop. If so print on the same row, if not store the new one & start a new row.

Hope that helps a little.

Older Comments
Newer Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

I'll keep your WordPress site up-to-date and working to its best.

Find out more