MySQL: Select all rows with the same ID, if a column from a joined table contains x

Question

(Giving an extremely simplified sample here, for the sake of clarity.)

I have a many-to-many relationship in a database (MySQL with Doctrine2/Symfony2 entities, but i'd like to know the answer in plain SQL), with a simple 2-column "join table" in between:

item
iditemname
1 Cat          
2 Mouse    

tag
idtagname          
1 Predator          
2 Prey                
3 Likes Cheese  
4 Flying              

item_tag
item_idtag_id
1          1        
1          3        
2          2        
2          3        

The standard join query for this:

SELECT itemname, tagname FROM item
JOIN item_tag ON  item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id

gives me all the data i'm interested in, with certain rows being double of course:

item
itemnametagname        
Cat          Predator        
Cat          Likes Cheese
Mouse     Prey              
Mouse     Likes Cheese

Now I need to filter this with a simple WHERE statement, so I add something like this:

WHERE tagname = 'prey'

But that only returns a single row of course. I need to get all rows for all items which have a 'prey tag' — so all the rows for the mouse item. The fact that the mouse likes cheese is a pretty important detail to know when you are searching for prey!

The point is of course to enter a search query for a certain tag name (without knowing the item names in advance), but once it returns a set of items, I would like to see all the other tags as well for the items in that set.

Is this possible with one query, or would I be forced to re-query to grab the remaining associated tags?

Answer 1

You can get the item_id from each prey like this:

SELECT it.item_id
FROM item_tag it
JOIN tag t ON t.id = it.tag_id AND t.tagname = 'prey';

Then, you can use that as a subquery in the `WHERE clause of your original query to get all rows for those items:

SELECT i.itemname, t.tagname
FROM item i
JOIN item_tag it ON it.item_id = i.id
JOIN tag t ON t.id = it.tag_id
WHERE i.id IN(
   SELECT it.item_id
   FROM item_tag it
   JOIN tag t ON t.id = it.tag_id AND t.tagname = 'prey');

Here is an SQL Fiddle example.