Question:

I have an SQL setup akin to the following:

ARTICLES

<ul><li>id (PK)</li> <li>name</li> </ul>

TAGS

<ul><li>id (PK)</li> <li>tag</li> </ul>

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

ARTICLE_TAG_ASSOCS

<ul><li>id (PK)</li> <li>article_id (FK)</li> <li>tag_id (FK)</li> </ul>

<strong>Question: how to find articles that have a particular tag?</strong>

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. <strong>But how can I eliminate that row completely?</strong>

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.

Although I don't fully understand your data requirement, GROUP_CONCAT doesn't seem like it is what you really want based on the information you have given.

But anywhoo.. perhaps you could use a subquery?

SELECT * FROM ( 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 ) A where a.tags is not null

Or a HAVING:

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 HAVING GROUP_CONCAT(CASE WHEN tag = 'some-tag' THEN tag ELSE NULL END) IS NOT NULL

<ol><li>Use proper joins (the syntax is 25 years old now). </li> <li>Use straightforward conditions without CASE etc. </li> <li>Add an extra join to tags <em>just</em> for the target tag. </li> </ol>

I assume that you want all tags, but at least one must be some-tag

SELECT name, GROUP_CONCAT(distinct t1.tag) AS tags FROM articles JOIN article_tag_assocs ON articles.id = article_id JOIN tags t1 ON t1.id = tag_id JOIN tags t2 ON t2.id = tag_id AND t2.tag = 'some-tag' GROUP BY name