mysql select update

Question

Got this:

Table a
ID RelatedBs
1  NULL
2  NULL

Table b
AID ID
1   1
1   2
1   3
2   4
2   5
2   6

Need Table a to have a comma separated list as given in table b. And then table b will become obsolete:

Table a
ID RelatedBs
1  1,2,3
2  4,5,6

This does not rund through all records, but just ad one 'b' to 'table a'

UPDATE a, b
SET relatedbs = CONCAT(relatedbs,',',b.id)
WHERE a.id = b.aid

UPDATE: Thanks, 3 correct answers (marked oldest as answer)! GROUP_CONCAT is the one to use. No need to insert commas between the ids using relatedids = CONCAT(relatedids,',',next_id) that is done automatic by GROUP_CONCAT.

Answer 1

You'll have to use the mysql group_concat function in order to achieve this: http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat

Answer 2

Look into GROUP_CONCAT(expr)

mysql> SELECT student_name,
    ->        GROUP_CONCAT(DISTINCT test_score
    ->                     ORDER BY test_score DESC SEPARATOR " ")
    ->        FROM student
    ->        GROUP BY student_name;

Answer 3

You can't do that in standard SQL. You could write a stored procedure to do that. I had a similar problem, but I was using PostgreSQL so I was able to resolve it by writing a custom aggregate function so that you can do queries like

select aid, concat(id) 
from b group by
aid

Update: MySQL has a group_concat aggregate function so you can do something like

SELECT id,GROUP_CONCAT(client_id) FROM services WHERE id = 3 GROUP BY id

as outlined here.