MySQL: Many To Many Relationships Part Two
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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 WHERE c.name = 'Emma Watson' ORDER BY c.c_id ASC |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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 WHERE f.title = 'The Canyon' ) AS cf ON c.c_id = cf.c_id ORDER BY c.c_id ASC |
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.
