[SOLVED] Merge multiple rows into single

[unxposed]

Hi,

 

I have three tables:

  users (cols: user_id, name)

  relationships (cols: rel_id, name)

  user_relationships (cols: id, user_id, rel_id)

 

My current sql is:

SELECT users.name, relationships.name AS relationship FROM users LEFT JOIN user_relationships ON users.user_id = user_relationships.user_id LEFT JOIN relationships ON user_relationships.rel_id = relationships.rel_id

 

Which returns along these lines:

 

name

relationship

bob

user

mary

user

mary

friend

mary

associate

 

My question is: How can I change this query to return something like this?:

 

name

relationship

bob

user

mary

user, friend, associate

 

Thanks in advance!

[unxposed]

SELECT users.name, GROUP_CONCAT(relationships.name SEPARATOR ', ')
FROM users
LEFT JOIN user_relationships ON users.user_id = user_relationships.user_id
LEFT JOIN relationships ON user_relationships.rel_id = relationships.rel_id
GROUP BY users.name