mysql GROUP_CONCAT DISTINCT multiple columns

Question

I have a tag field for a blog posts. tags have unique id but their displayName might be duplicated. What I want is a query that selects posts and in all_tags field we get couples of (id,displayName) is this way:

id1,name1;id2,name2;id3,name3

My query looks like:

select ....
CONCAT_WS(';', DISTINCT (CONCAT_WS(',',tags.id,tags.displayName))) AS all_tags
Join ...post content ...
Join ...post_tags ...
Join ...tags ...
ORDER BY posts.id

This line causes problem:

CONCAT_WS(';', DISTINCT (CONCAT_WS(',',tags.id,tags.displayName))) AS all_tags

How should I modify it?

Some people use an inner (SELECT .. FROM) but as I have heard, it is so inefficien

SELECT `posts`.*,`categories`.*,`creators`.*,`editors`.*
CONCAT_WS(';', DISTINCT GROUP_CONCAT(CONCAT_WS(',',tags.id,tags.displayName))) AS all_ids
FROM (`posts`) 
LEFT JOIN `languages` ON `posts`.`language_id`=`languages`.`id` 
LEFT JOIN `users` as creators ON `posts`.`creatorUser_id`=`creators`.`id` 
LEFT JOIN `users` as editors ON `posts`.`lastEditorUser_id`=`editors`.`id` 
LEFT JOIN `userProfiles` as editors_profile ON `editors`.`profile_id`=`editors_profile`.`id` 
LEFT JOIN `categories` ON `posts`.`category_id`=`categories`.`id` 
LEFT JOIN `postTags` ON `postTags`.`post_id`=`posts`.`id` 
LEFT JOIN `tags` ON `postTags`.`tag_id`=`tags`.`id` 
LEFT JOIN `postTags` as `nodetag_checks` ON `nodetag_checks`.`post_id`=`posts`.`id` 
LEFT JOIN `tags` as `tag_checks` ON `nodetag_checks`.`tag_id`=`tag_checks`.`id` 
WHERE ( 9 IN(`tag_checks`.`id`,`tag_checks`.`cached_parents`) OR 10 IN(`tag_checks`.`id`,`tag_checks`.`cached_parents`) OR 11 IN(`tag_checks`.`id`,`tag_checks`.`cached_parents`)) 
GROUP BY `posts`.`id` ORDER BY `posts`.`created` desc LIMIT 0, 20

can we see the whole query? depends on what inner select you are using. — John Woo, Sep 17 '13 at 08:45
possible duplicate of [Multiple GROUP\_CONCAT on different fields using MySQL](http://stackoverflow.com/questions/7506750/multiple-group-concat-on-different-fields-using-mysql) — Blazemonger, Feb 03 '14 at 22:52

Stephan · Accepted Answer · 2015-06-09T10:33:25.997

113

Try this:

GROUP_CONCAT(
  DISTINCT CONCAT(tags.id,',',tags.displayName) 
  ORDER BY posts.id 
  SEPARATOR ';'
)

edited Jun 09 '15 at 10:33

answered Sep 17 '13 at 08:48

Stephan

8,000
3
36
42

You most likely meant `CONCAT(tags.id, ',', tags.displayName)`. Also `GROUP BY post.id` instead of `ORDER BY post.id` are in order. – peterm Sep 17 '13 at 08:51
@peterm actually no, the op wants a single string returned like "id1,name1;id2,name2;id3,name3..." – Stephan Sep 17 '13 at 08:53
2

`GROUP_CONCAT(DISTINCT CONCAT(tags.id,',',tags.displayName) ORDER BY posts.id SEPARATOR ';')` – werva Sep 17 '13 at 08:59
@Stephan may I ask you why `ORDER BY` is necessary here? – werva Sep 17 '13 at 09:20
@werva i saw it in your query and i thought you wanted the data to be order by post id... you can remove it if its not needed – Stephan Sep 17 '13 at 09:22
16

Note that if you don't need DISTINCT, you don't need CONCAT, as GROUP_CONCAT is able to concat multiple fields : `GROUP_CONCAT(tags.id, ',', tags.displayName)` – Matthieu Oct 10 '16 at 15:24
@Matthieu make this an answer. – Willa Feb 07 '20 at 09:33

score 6 · Answer 2 · answered Feb 16 '20 at 10:42

As advised by @Willa, I add my comment as an anwser :

GROUP_CONCAT allows you to concat multiple fields :

GROUP_CONCAT(tags.id, ',', tags.displayName)

The only difference with Stephan's answer is in case your code allows the same tag to be affected several times to one post OR if you JOIN sequence leads you to multiselect the same record in the tag table. In those case, my solution will return the same tags multiple times.

Julesezaar · Answer 3 · 2022-01-26T21:55:29.367

On top of @Stephan's great answer, to prevent the same content showing up multiple times due to multiple JOIN's in your query but you don't want the id to show in the output...

GROUP_CONCAT(
    DISTINCT
    tags.displayName,
    '||__', tags.id, '__||'
    SEPARATOR '\n'
)

And then loop over the result in the end removing everything between ||__ and __|| .

This example is for php:

$data = preg_replace("/\|\|__.*__\|\|/", '',  $data);

mysql GROUP_CONCAT DISTINCT multiple columns

3 Answers3

Linked