CODEWITHSUNDEEP
mysql
newbieProgrammer
newbieProgrammer

Reputation: 227

How do I select posts that match all the required tags?

I have a table with two columns, blog post ids and tag ids. A blog post can have several tags like it's a post about "New York" and "Soho". Let's say I want to find all the blog posts that have BOTH tag id 1 and 2, how do I do that?

blogPostToTags
=============================
id           |tagId
-----------------------------
1            |1
1            |2
2            |1
3            |2

UPDATE:

I've tried the following:

SELECT id FROM blogPostToTags WHERE tagId = 1 AND tagId = 2

and

SELECT id FROM blogPostToTags WHERE tagId = 1 OR tagId = 2

neither work...

Upvotes: 0

Views: 345

Answers (3)

Timo Huovinen
Timo Huovinen

Reputation: 55673

Something along the lines of this should work

SELECT * FROM items i
WHERE EXISTS (
SELECT NULL
FROM blogPostToTags tg
WHERE tg.tagID IN ({tag_comma_separated_values})
AND tg.itemID = i.id
GROUP BY tg.itemID
HAVING COUNT(tg.tagID) = {tag_count}
)

in your case

SELECT * FROM items i
WHERE EXISTS (
SELECT NULL
FROM blogPostToTags tg
WHERE tg.tagID IN (1,2)
AND tg.itemID = i.id
GROUP BY tg.itemID
HAVING COUNT(tg.tagID) = 2
)

Upvotes: 0

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230551

Use double JOIN.

SELECT id
FROM blogPosts p
INNER JOIN blogPostToTags pt1 ON pt1.id = p.id
INNER JOIN blogPostToTags pt2 ON pt2.id = p.id
WHERE pt1.tagId = 1 AND pt2.tagId = 2;

Upvotes: 2

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385405

SELECT `id`, COUNT(*) FROM
    (SELECT `id` FROM `blogPostToTags` WHERE `tagId` = 1)
    UNION
    (SELECT `id` FROM `blogPostToTags` WHERE `tagId` = 2)
    AS `_`
    -- this temporary table contains an entry for each `id` when
    -- the tag ID matches. I assume that `id`,`tagId` is a UNIQUE key.
    -- COUNT(*) will be '2' for any `id` with both matches.
WHERE `n` = 2
GROUP BY `id`

I don't have an installation of MySQL handy so this is not tested, but it is instead a best-guess designed to send you on the way.

Upvotes: 0

Related Questions