Retrieve Record When All Of Values Exist in Association Table

Question

posts
id

post_tags
post_id
tag

I already have my "One of" query to match at least one of a passed in array of tags like so:

SELECT p.* 
FROM posts p 
WHERE EXISTS (SELECT 1 FROM tag t WHERE t.post_id = p.id AND t.tag IN ('tag1', 'tag2'))

How do I do my "All of" query so that all of my passed in tags are contained in the post_tags association table for a post?

Answer 1

Use a CTE that returns the tags that you want to search for, join the tables and select only the rows containing the tags of the CTE, group by post and set the condition in the HAVING clause so that only posts related to all the tags are returned:

WITH cte(tag) AS (VALUES ('tag1'), ('tag2'))
SELECT p.* 
FROM posts p INNER JOIN post_tags t
ON t.post_id = p.id
WHERE t.tag IN cte
GROUP BY p.id
HAVING COUNT(DISTINCT t.tag) = (SELECT COUNT(*) FROM cte);

If tag is unique in post_tags you may remove DISTINCT from COUNT(DISTINCT t.tag).

See a simplified demo.