mysql concat 条件,MySQL：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.

解决方案

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.