Q: Group all rows with same values but concat unique values, many to many relation I have this table user:
id | name
1  ,  Mark

and table specialization
id | spec
1  ,  addictions
2  ,  psycho

and this table connecting user and specialization: user_specialization
user | specialization
1    ,        1
1    ,        2

I want to get the list of all users and their specializations
Mark, specialization: addiction, psycho
I also want to get those users that dont have any specializations
I have this SQL
SELECT name, spec 
FROM user 
LEFT JOIN user_specialization ON user.id = user_specialization.user 
LEFT JOIN specialization ON user.specialization.specialization = specialization.id

And this gives me this result
name | spec
Mark , addictions
Mark , psycho

I need this:
name | spec
Mark | addictions, psycho

How can I acheive that? I suppose that i will need GROUP BY name but i dont know how to concat those spec rows.

A: You can use group_concat as follows:
SELECT name, group_concat(spec)
FROM user 
LEFT JOIN user_specialization ON user.id = user_specialization.user 
LEFT JOIN specialization ON user.specialization.specialization = specialization.id
Group by name

