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
User #2 Grp3,Grp1

This is my query

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

There are actually three tables involved:

ft_smsender_persons2groups - This table holds key from both the tables

Answer Source

You can use the group_concat function:

FROM     ft_smsender_persons AS p
JOIN     ft_smsender_persons2groups AS p2g ON = p2g.person_id 
JOIN     ft_smsender_groups AS g ON = p2g.group_id
WHERE    deleted = 0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download