MySQL: Many To Many Relationships Part Two

/ Misc / by Paul Robinson / 0 comments
This post was published back on February 18, 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.

I’ve gotten quite a few questions regarding how to filter results from a many to many query. First, what do I mean by filter? Well, if we take the films database we used in the last tutorial, I mean find out what films a certain Celebrity has been in. Maybe we could find out who was in a certain film instead. That’s what I mean by filter the results. So let’s take a look at how to do it.

Filtering Results From Many To Many Queries

It’s always important to remember that you should try to get as near as possible to the results you want when querying MySQL. This helps speed things up as filtering results via your server side scripting language is always slower than filtering your results via your MySQL query.

Okay let’s start by taking a look at how we would grab all the films that Emma Watson has starred in. As you may have noticed I’ve added some more rows to our Films & Celebrities tables to help give a richer result set.

If you have read part one you’ll probably recognize this query. If not I suggest you journey back & read part one of our many to many relationships posts.

There is just one slight difference and that is the WHERE clause. This is what we use to return only the results we want. Now while the WHERE clause is one of the first things you learn in MySQL using it inside a large query can be difficult as it’s sometimes hard to figure out where to place it. Here are the results that were returned:

name title
Emma Watson Harry Potter and the Philosopher’s Stone
Emma Watson Harry Potter and the Chamber of Secrets
Emma Watson Harry Potter and the Prisoner of Azkaban
Emma Watson Harry Potter and the Goblet of Fire
Emma Watson Harry Potter and the Order of the Phoenix
Emma Watson Harry Potter and the Half-Blood Prince

That’s all there is to it. If you wanted to filter by film you would place the WHERE clause after the ON inside the sub query. So it would look like this:

If you are having problems understanding how this works properly feel free to ask or try creating test queries in phpmyadmin. The latter is one of the ways I used to help me understand complex queries.

As always if you have any questions about anything here, or you want to ask about something I haven’t covered. Let me know in the 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