How to make a query with AND operator between rows

Question

I have the following table, witch is the relationship pivot table, between posts and tags:

post_tags table

I need to get the posts that strict contain a 'X' given tag(s).

For example:

• If I need posts with exclusively tags 1 and 2, it should returns post_id 1 and 4.

• If I need post with tag 2, it should only returns post_id 3.

• If I need post with tag 23, it should't returns nothing.

I've tried with:

SELECT * FROM `post_tags` WHERE tag_id = 1 OR tag_id = 2;

but obviously it returns all post_id with these tags_id

And with:

SELECT * FROM `post_tags` WHERE tag_id = 1 AND tag_id = 2;

It doest's return anything, because it's trying to comparate between columns.

Any solution?

Answer 1

You need to group by post_id and check the conditions in the having clause:

SELECT post_id
FROM post_tags
GROUP BY post_id
HAVING 
  SUM(tag_id NOT IN (1, 2)) = 0
  AND
  COUNT(DISTINCT tag_id) = 2

This will return only posts with tags 1 and 2 and no other tag.
For posts with only tag 2:

SELECT post_id
FROM post_tags
GROUP BY post_id
HAVING 
  SUM(tag_id <> 2) = 0
  AND
  COUNT(DISTINCT tag_id) = 1

If each post_id, tag_id pair is unique, then you can do this:

SELECT post_id
FROM post_tags
GROUP BY post_id
HAVING COUNT(tag_id IN (1, 2)) = COUNT(tag_id)

Answer 2

You could use a correlated subquery:

SELECT *
FROM post_tags pt
WHERE (
        SELECT count(*) 
        FROM post_tags
        WHERE pt.post_id = post_id
            AND post_tag IN (1, 2)

        ) = 2

Or you could use an in list (using similar logic):

SELECT *
FROM post_tags pt
WHERE post_id IN (
        SELECT post_id
        FROM post_tags
        WHERE post_tag IN (1, 2)
        GROUP BY post_id
        HAVING count(*) = 2
        )