How to JOIN two tables with one mandatory condition and an optional one

Question

I have a media table and a tag_media table. The tag_media table is a relationship table that contains the columns id_tag and id_media. A single media file can be tagged with multiple tags. Something like:

tag_media:
    id_tag
    id_media

media:
    id_media
    (etc, etc)

I need a query that will allow me to fetch all media that was tagged with a set of mandatory tags and a set of optional tags so that I can guarantee that the returned media were tagged with ALL the mandatory tags and AT LEAST one of the optional tags.

How can I do this?

Answer 1

This query will do what you are looking for:

SELECT
   M.id_media
FROM
   media M
   INNER JOIN tag_media T ON M.id_media = T.id_media
WHERE T.id_tag IN ('required1', 'required2', ... 'optional1', 'optional2', ...)
GROUP BY M.id_media
HAVING
   Sum(T.id_tag IN ('required1', ... 'requiredn')) = <n>
      -- where n is the required number of tags
   AND Sum(T.id_tag IN ('optional1', ... 'optionaln')) >= 1

I prefer constructions like this, though, because then the information is listed only once:

SELECT
   M.id_media
FROM
   media M
   INNER JOIN tag_media T ON M.id_media = T.id_media
   INNER JOIN (
      SELECT 'required1' id_tag, 1 required UNION ALL SELECT 'required2', 1 ...
      UNION ALL SELECT 'optional1', 0 UNION ALL SELECT 'optional2', 0
   ) S ON T.id_tag = S.id_tag
GROUP BY M.id_media
HAVING
   Sum(required) = <n> -- where n is the required number of tags
   AND Sum(1 - required) >= 1

And if you can use CTEs in MySQL, then converting the S derived table to a CTE (or putting it in a temp table) will let you change <n> from the literal number of required options to (SELECT Count(*) from S).

Note: technically, these queries can be rewritten to be entirely against the tag_media table. But if you want to pull other information from the media table, then this is how you'd probably do it.