MySQL: Optionally Build Group Concat field

Question

I have a set of articles. Some of these articles have been assigned x number of tags.

I want to select all articles in the format specified in the SELECT, and if the article also has any number of tags assigned, put them into a single GROUP_CONCAT field.

The SQL below does everything I need, except it only retrieves articles that have at least one tag.

How can I make it so that the tagging requirement is optional?

SELECT d.entry_id AS entry_id
   , d.field_id_40 AS body
   , t.title AS title
   , t.url_title AS url_title
   , t.entry_date AS entry_date
   , GROUP_CONCAT(g.tag_name ORDER BY g.tag_name) AS tag
FROM exp_channel_data d
   , exp_category_posts c
   , exp_channel_titles t
   , exp_tagger_links l
   , exp_tagger g
WHERE t.YEAR > '2005'
   AND t.site_id = d.site_id
   AND d.site_id = 9
   AND ( c.cat_id = 95
      OR c.cat_id = 93
      OR c.cat_id = 64
      OR c.cat_id = 24
   )
   AND d.entry_id = t.entry_id
   AND t.entry_id = c.entry_id
   AND t.STATUS = 'open'
   AND l.tag_id = g.tag_id
   AND d.entry_id = l.entry_id
GROUP BY d.entry_id

Use `JOIN`, not `WHERE`, for joining your tables. Then, it will be easy to change an `INNER JOIN` to `LEFT JOIN` to solve your problem. — ypercubeᵀᴹ, Dec 07 '11 at 21:46

score 3 · Accepted Answer · answered Dec 07 '11 at 21:53

Switching your JOINS to use the JOIN syntax, this should provide you with your answer.

SELECT d.entry_id AS entry_id
   , d.field_id_40 AS body
   , t.title AS title
   , t.url_title AS url_title
   , t.entry_date AS entry_date
   , GROUP_CONCAT(g.tag_name ORDER BY g.tag_name) AS tag
FROM exp_channel_data AS d
INNER JOIN exp_channel_titles AS t ON d.site_id = t.site_id
   AND d.entry_id = t.entry_id
INNER JOIN exp_category_posts AS c ON t.entry_id = c.entry_id
LEFT OUTER JOIN exp_tagger_links AS l ON d.entry_id = l.entry_id
LEFT OUTER JOIN exp_tagger AS g ON l.tag_id = g.tag_id
WHERE t.YEAR > '2005'
   AND d.site_id = 9
   AND c.cat_id IN (95, 93, 64, 24)
   AND t.STATUS = 'open'
GROUP BY d.entry_id

You may also want to look at A visual explanation of SQL joins.

MySQL: Optionally Build Group Concat field

1 Answers1