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?

Answer 1

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