14129

MySQL: condition on result of GROUP_CONCAT?

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.

Answer1:

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.

Answer2:

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

Answer3:

<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

Recommend

  • Performance Tuning SQL
  • Groovy: Unexpected token “:”
  • How to write order and limit within cakephp joins array
  • Custom validator control occupying space even though display set to dynamic
  • Word Open XML Mail Merge
  • Replace value with Factor in r data.table
  • Android fill_parent issue
  • FileReader+canvas image loading problem
  • How to access EntityManager inside Entity class in EJB3
  • R - Combining Columns to String Based on Logical Match
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Pass value from viewmodel to script in zk
  • Using $this when not in object context
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Deselecting radio buttons while keeping the View Model in synch
  • Getting last autonumber in access
  • Read text file and split every line in MSBuild
  • How to make a tree having multiple type of nodes and each node can have multiple child nodes in java
  • Counter field in MS Access, how to generate?
  • Incrementing object id automatically JS constructor (static method and variable)
  • How to check if every primary key value is being referenced as foreign key in another table
  • Can I have the cursor start on a particular column by default in jqgrid's edit mode?
  • How to make Safari send if-modified-since header?
  • jquery mobile loadPage not working
  • Rearranging Cells in UITableView Bug & Saving Changes
  • Return words with double consecutive letters
  • How to pass list parameters for each object using Spring MVC?
  • Circular dependency while pushing http interceptor
  • how to add data labels for bar graph in matlab
  • Linker errors when using intrinsic function via function pointer
  • How to delete a row from a dynamic generate table using jquery?
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • How to stop GridView from loading again when I press back button?
  • LevelDB C iterator
  • JaxB to read class hierarchy
  • How can i traverse a binary tree from right to left in java?