Search code examples
sqlsqlitejoingroup-byhaving

Query to return all of the associated MTM elements but only if a specific one is among them

I have 3 tables associated like this:

:Manga
| id | title     | author          |
|----+-----------+-----------------|
|  1 | Initial D | Yuki Urushibara |

:Tags            :Manga_Tags_Association
| id | name   |  | tag_id | manga_id |
|----+--------|  |--------+----------|
|  1 | racing |  |      1 |        1 |
|  2 | sports |  |      2 |        1 |
|  3 | chill  |  |      3 |       23 |
                 |      1 |        5 |

Where the third one represents the association between manga entries and tags (so 'Initial D' is tagged as both 'racing' and 'sports'

I already have queries to search and filter in different ways, but I'm struggling really hard at filtering by a tag while returning a result like this, preserving all of the tags associated tags:

| M.id | M.title   | tags                  |
|------+-----------+-----------------------|
|    1 | Initial D | racing, sports        |
|   55 | REDLINE   | sports, racing, scifi |

How can I do this if, let's say, I'm filtering by tags that are equal to 'racing'? My attempts either exclude all of the tags that are not the one specified from the results, looking like this...

| M.id | M.title   | tags   |
|------+-----------+--------|
|    1 | Initial D | racing |
|   55 | REDLINE   | racing |

...or don't work at all, like the ones I've tried thinking I can check the contents of GROUP_CONCAT (SELECT ..., GROUP_CONCAT(Tags.title) AS AllTags ... something something ''racing' IN AllTags;) given it's not a table. I also couldn't get this done by doing a subquery listing all of the tags related to a Manga.id and checking if any of the rows contains 'racing', because I don't know if that's even possible (I couldn't find how). Nor using HAVING 'racing' IN a subquery aliased AllTags (same issue as before, it complains it's not a table).

Edit: OK I just saw some things about windows, I'm experimenting with that.

Solution

  • Join the tables, aggregate and set the condition in the HAVING clause:

    SELECT m.id, m.title,
       GROUP_CONCAT(t.name) tags
FROM Manga m
INNER JOIN Manga_Tags_Association mta ON mta.manga_id = m.id
INNER JOIN Tags t ON t.id = mta.tag_id
GROUP BY m.id
HAVING MAX(t.name = 'racing') = TRUE; -- just HAVING MAX(t.name = 'racing') will also do