Reuse result of a correlated subquery

Question

I have a table media_tags containing two columns linking media to user defined tags. I want to do some queries selecting media with complicated conditions on the tags. My current solution is using the same correlated subquery multiple times.

Multiple SO answers have pointed me to CTEs but as far as i understand it those can only be used to replace uncorrelated subqueries.

SELECT outer.media_id
FROM media_tags AS outer
WHERE 1 IN (SELECT tag_id FROM media_tags AS inner WHERE outer.media_id = inner.media_id)
AND 2 NOT IN (SELECT tag_id FROM media_tags AS inner WHERE outer.media_id = inner.media_id)
AND (3 IN (SELECT tag_id FROM media_tags AS inner WHERE outer.media_id = inner.media_id)
OR 4 IN (SELECT tag_id FROM media_tags AS inner WHERE outer.media_id = inner.media_id));

Answer 1

You can do a self join, GROUP BY media_id and set the conditions in the HAVING clause:

SELECT outer.media_id
FROM media_tags AS outer INNER JOIN media_tags AS inner
ON outer.media_id = inner.media_id
GROUP BY outer.media_id
HAVING 
  SUM(inner.tag_id = 1) > 0
  AND
  SUM(inner.tag_id = 2) = 0
  AND
  SUM(inner.tag_id IN (3, 4)) > 0

or even simpler without the self join check if this works also:

SELECT media_id
FROM media_tags
GROUP BY media_id
HAVING 
  SUM(tag_id = 1) > 0
  AND
  SUM(tag_id = 2) = 0
  AND
  SUM(tag_id IN (3, 4)) > 0