John John - 3 years ago 133
MySQL Question

query to show one column names in comma seperated list

I am working in mysql database and i need some help in listing down one column data values to be shown as comma separated, Actually there are two tables: one is persons table and the other one is groups table, now one person can belong to many groups, so i am trying to show the users data.

I want to show the data like this:

UserName Group Email
User #1 Grp1,Grp3,Grp5 user1@email.com
User #2 Grp3,Grp1 user2@email.com


This is my query

select ft_smsender_groups as g left join (select p1.id,p2.group_id
from ft_smsender_persons2groups as p2 inner join ft_smsender_persons
as p1 on p1.id = p2.person_id ) as p2 on g.id = p2.group_id','g.id,g.name,
created,updated where
deleted = 0 group by id,name


There are actually three tables involved:

ft_smsender_groups
ft_smsender_persons2groups - This table holds key from both the tables
ft_smsender_persons

Answer Source

You can use the group_concat function:

SELECT   p.name, p.email, GROUP_CONCAT(g.name) AS groups
FROM     ft_smsender_persons AS p
JOIN     ft_smsender_persons2groups AS p2g ON p.id = p2g.person_id 
JOIN     ft_smsender_groups AS g ON g.id = p2g.group_id
WHERE    deleted = 0
GROUP BY p.name, p.email
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download