Select query with GROUP_CONCAT in mysql

Question

i have two tables look like that.

users

id       name
10       dani
 9       mark
 8       himel
 7       dipu

config_project

id    assignee_web  assignee_app  assignee_qa
29       10,7,9       10,7          10,9
28        10,8       9,10,7          8

here, assignee_web, assignee_app and assignee_qa all are the reference id with id of users table. now i want a select query which output will look like

id    assignee_web         assignee_app          assignee_qa
29   dani,dipu,mark         dani, dipu            dani,mark
28     dani,himel           dani,mark,dipu         himel

i have wriiten a query which displays only assignee_web. here is my code

SELECT c.id as id, GROUP_CONCAT(u.name SEPARATOR ', ') as assignee_web FROM config_project c, users u
        WHERE FIND_IN_SET(u.id, c.assignee_web)
        GROUP BY c.id ORDER BY c.id DESC

I can get only assignee_web data but can't return data for assignee_app and assignee_qa with this query.

Answer 1

You can use correlated queries:

SELECT c.id as id,
       (SELECT GROUP_CONCAT(u.name SEPARATOR ', ') 
        FROM users u
        WHERE FIND_IN_SET(u.id, c.assignee_web)) as assignee_web,
       (SELECT GROUP_CONCAT(u.name SEPARATOR ', ') 
        FROM users u
        WHERE FIND_IN_SET(u.id, c.assignee_app)) as assignee_app,
       (SELECT GROUP_CONCAT(u.name SEPARATOR ', ') 
        FROM users u
        WHERE FIND_IN_SET(u.id, c.assignee_qa)) as assignee_qa                
FROM config_project c
ORDER BY c.id DESC

Though you should normalize your data. People just never learn, and each time you are having some new problems, and you will keep on having them.