*UPDATE, seems like the answer was given, but the SQL query his erroring out.. can anyone help? See the first answer, i posted the problem there.

So to put this simply. I have 3 tables. An "item" table and a "tag" table. Then I also have an "item_tag" table which ties the 2 together.

I want to make a query that lists all the items that have particular tags assigned to it. So I would like the query to list all items that have tag x and tag y applied to it.

This is what I have come up with so far.. except that this will list any that match either tag id 148 or tag id 152. If I make it say "AND" it shows no results.

SELECT *
FROM (`item`)
RIGHT OUTER JOIN `item_tag` ON `item`.`id` = `item_tag`.`fk_item_id`
WHERE `item_tag`.`fk_tag_id` = "152" OR `item_tag`.`fk_tag_id` = "148"
GROUP BY `item`.`id`

Comments

Lol. Of course it returns nothing when you have id=152 AND id=148 in the query. That's a logical impossibility.

Written by Franz

So... maybe offer a solution?

Written by Eric J.

I'm working on it. Sorry to be so rude.

Written by Franz

wow. your right. i dont even know why i questioned that part haha.. woops

Written by Roeland

Accepted Answer

You can get ids of the items that have all of the tags you want using this query:

SELECT fk_item_id
FROM item_tag
WHERE fk_tag_id IN (5,10,15)
GROUP BY fk_item_id
HAVING COUNT(*) = 3

And then just

SELECT * 
FROM item 
WHERE id 
IN 
(
    SELECT fk_item_id
    FROM item_tag
    WHERE fk_tag_id IN (5,10,15)
    GROUP BY fk_item_id
    HAVING COUNT(*) = 3
)

You just have to modify the ids and the 3 which is the count of those ids.

When your table does not have UNIQUE constraint (it should have) and there can be the same tags in particular item you should modify the query to this:

SELECT * 
FROM item 
WHERE id 
IN 
(
    SELECT fk_item_id
    FROM ( SELECT DISTINCT fk_item_id, fk_tag_id FROM item_tag ) someAlias
    WHERE fk_tag_id IN (5,10,15)
    GROUP BY fk_item_id
    HAVING COUNT(*) = 3
)
Written by Lukasz Lysik
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki