The IN()
functionoperator is equivalent to a series of OR
, which doesn't help you at all here.
Instead, I would build the query in a manner like this.
SELECT posts.id
FROM posts
WHERE posts.id IN (SELECT post_id FROM post_categories WHERE category_id=1) AND
posts.id IN (SELECT post_id FROM post_categories WHERE category_id=2) AND
posts.id IN (SELECT post_id FROM post_categories WHERE category_id=3)
Admittedly, not a pretty construct, but I wrote it for readability. The following query might perform better:
SELECT posts.id
FROM posts
-- include:
WHERE posts.id IN (
SELECT post_id
FROM post_categories
WHERE category_id IN (1, 2, 3)
GROUP BY post_id
HAVING COUNT(*)=3)
-- exclude:
AND posts.id NOT IN (
SELECT post_id
FROM post_categories
WHERE category_id NOT IN (1, 2, 3))
The second query assumes that the primary key of post_categories
is (post_id, category_id)
. Since you haven't specified which rdbms you're running, you may have to tweak my code a bit to make it run.
Edit: The -- exclude:
part eliminates posts that have any other category_id than 1, 2 or 3. You may want to skip this part depending on if you want to return a) all posts that have categories 1, 2 and 3 or b) all posts that have exactly categories 1, 2 and 3 and no other categories.