MySQL Results as comma separated list

Question

I need to run a query like:

SELECT p.id, p.name, (SELECT name FROM sites s WHERE s.id = p.site_id) AS site_list FROM publications p

But I'd like the sub-select to return a comma separated list, instead of a column of data. Is this even possible, and if so, how?

Answer 1

You can use GROUP_CONCAT to perform that, e.g. something like

SELECT p.id, p.name, GROUP_CONCAT(s.name) AS site_list
FROM sites s
INNER JOIN publications p ON(s.id = p.site_id)
GROUP BY p.id;

Answer 2

Great answer... but... I have something like...

ID INDEX VALUE
1  0     4
1  1     7
1  2     8
1  3     9

...

so...

select ID, count(ifnull(INDEX,0)), group_concat(ifnull(INDEX,0)), group_concat(ifnull(VALUE)) from TABLE
group by ID order by ID, ifnull(INDEX, 0)

and I get back:

1  0,1,2,3  4,7,8,9

While that's correct, for some reason, I NEED the count or I don't get the group_concats in the correct order. (Potentially because that forces all of the rows to be read?)

They'll be like:

1  1,2,3,0  7,8,9,4

But, the INDEX order is important! I think I would like very much to not have to count them if possible. Or, better yet, I need to be sure I'm getting them in the correct order--using the count makes me worried I'm just accidentally getting the correct order!

Thoughts?

thanks jason.