Search code examples
mysqldatabasejoingroup-concat

MySQL: condition on result of GROUP_CONCAT?

I have an SQL setup akin to the following:

ARTICLES

  • id (PK)
  • name

TAGS

  • id (PK)
  • tag

...and a third table logging associations between the two, since there can be multiple tags to each article:

ARTICLE_TAG_ASSOCS

  • id (PK)
  • article_id (FK)
  • tag_id (FK)

Question: how to find articles that have a particular tag?

The best I could come up with was this:

SELECT
    name, GROUP_CONCAT(CASE WHEN tag = 'some-tag' THEN tag ELSE NULL END) AS tags
FROM
    articles, tags, article_tag_assocs
WHERE
    articles.id = article_id && tags.id = tag_id && tags IS NOT NULL
GROUP BY
    article_id

That's on the right lines; if an article doesn't have the tag "some-tag" then that column shows up with a null value in the "tags" column. But how can I eliminate that row completely?

Naturally, I tried appending

&& tags NOT LIKE '%some-tag%'

...to my WHERE clause, before learning that you can't use GROUP_CONCAT aliases in WHERE clauses. So I tried appending:

HAVING tags IS NOT NULL;

...to the query, with the same result, i.e. MySQL says it doesn't recognise the column "tags".

Any help appreciated.

Solution

  • How about this?

    SELECT distinct a.name
FROM articles a join
     article_tag_assocs ata
     on a.id = ata.article_id join
     tags t
     on t.id = ata.tag_id
WHERE t.tag = 'some-tag';

    It answers your question quite directly.

    Note that this also introduces table aliases. These make the query easier to write and to read.