How to properly collapse MySQL rows into a single list

Question

I have a query of the form

SELECT pub.id, topic.id, pub.title, etc... 
FROM pub, topic, link
WHERE (matching logic)

which returns in the form

pub.id  |  topic.id  |  pub.title
---------------------------------
x1      |  a         |  y1
x1      |  b         |  y1
x1      |  ...       |  y1

x2      |  c         |  y2
x2      |  d         |  y2
x2      |  ...       |  y2

what I really want is a response of the form

pub.id  |  topic.id  |  pub.title
---------------------------------
x1      |  a, b ...  |  y1
x2      |  c, d ...  |  y2

(I don't think the matching logic is important, but I'll post it if necessary.)

I've tried group_concat() and coalesce, but nothing works. Any help?

How you have tried `group_concat` can you post that query ? – M Khalid Junaid Jun 19 '14 at 15:39 — M Khalid Junaid, Jun 19 '14 at 15:39

score 4 · Accepted Answer · answered Jun 19 '14 at 15:44

4

Did you remember to include GROUP BY in your GROUP_CONCAT test?
You cannot concatenate a group if you don't group the data.

Here's a working example: http://sqlfiddle.com/#!2/5cd63/2

It should amount to:

SELECT pub.id, GROUP_CONCAT(topic.id SEPARATOR ','), pub.title
FROM pub, topic
WHERE (matching logic)
GROUP BY pub.id, pub.title

answered Jun 19 '14 at 15:44

Mr. Llama

20,202
2
62
115

This is perfect, thank you! I had entirely forgotten about the `GROUP BY`, of course. – James Adam Buckland Jun 19 '14 at 16:01

How to properly collapse MySQL rows into a single list

1 Answers1