Select Page

MySQL group_concat explanation with real example

by | Apr 17, 2022 | MySQL

MySQL group_concat is an aggregate function that join multiple strings into one single string. Some time we may need to show retrieve data as a unit. For example we can take WordPress post and categories. A post can have multiple categories. So if we want to retrieve a single post along with it’s associative categories then we have to make a JOIN Query here like this:

SELECT
  p.ID,
  p.post_title,
  p.post_author,
  term.name
FROM
  lms_posts as p
  INNER JOIN lms_term_relationships as term_rel ON term_rel.object_id = p.ID
  INNER JOIN lms_terms as term ON term.term_id = term_rel.term_taxonomy_id
WHERE
  p.ID = 4588

Above SQL query is fine and should get data like below:

MySQL result may vary, by the way main point is here we can see four rows. But it would be great if we can get all categories in one column instead of multiple rows.

So update the above query with group_concat:

SELECT
  p.ID,
  p.post_title,
  p.post_author,
  group_concat(term.name) as category
FROM
  lms_posts as p
  INNER JOIN lms_term_relationships as term_rel ON term_rel.object_id = p.ID
  INNER JOIN lms_terms as term ON term.term_id = term_rel.term_taxonomy_id
WHERE
  p.ID = 4588

So now we can see the result is absolutely fine

All the categories in one column. We don’t need to loop through multiple rows anymore. Hope it is really helpful 😀