How to concat find_in_set results with comma separated string in mysql

Question

I have used FIND_IN_SET to my below select sql query and got the result but I want to store industrie_branch comma separated list

Select Query:

SELECT i.instagrammer_id
      , CONCAT(i.first_name,' ',i.last_name) AS instagrammer_name
      , FLOOR(DATEDIFF (NOW(), i.date_of_birth)/365) AS age
      , i.date_of_birth
      , i.gender
      , i.email
      , i.created_date
      , b.name_en AS industrie_branch
FROM instagrammer AS i
LEFT JOIN industries_branches b ON FIND_IN_SET(b.industrie_branch_id, i.industrie_branch)
WHERE i.status = 1
ORDER BY instagrammer_name DESC

and getting this output of above query:

instagrammer_id     instagrammer_name       age     date_of_birth       gender      email               industrie_branch
***************     *****************       ***     *************       ******      ****************    ****************
37                  Jack                    27      1988-09-30          male        jack@hotmail.com    Food
37                  Jack                    27      1988-09-30          male        jack@hotmail.com    Photography
38                  Tom                     27      1988-09-30          male        tom@gmail.com       Lifestyle
38                  Tom                     27      1988-09-30          male        tom@gmail.com       Luxury

but I want this output with comma separated list for industrie_branch:

instagrammer_id     instagrammer_name       age     date_of_birth       gender      email               industrie_branch
***************     *****************       ***     *************       ******      ****************    ****************
37                  Jack                    27      1988-09-30          male        jack@hotmail.com    Food, Photography
38                  Tom                     27      1988-09-30          male        tom@gmail.com       Lifestyle, Luxury

Never store data as comma separated values - it will only cause you lots of trouble. One value/row is the SQL way! — jarlh, Nov 24 '15 at 08:08
Send us your mysql data sample using [SQLFiddel](http://sqlfiddle.com/) — Supun Silva, Nov 24 '15 at 08:13
Jarlh is correct. However as a short term hack you can wrap this query up inside another, with GROUP_CONCAT — Strawberry, Nov 24 '15 at 08:26
Just use this thread to get your desired output, not exactly but helpful. http://stackoverflow.com/questions/18113803/mysql-group-concat-group-by-on-multiple-fields — Muhammad Muazzam, Nov 24 '15 at 08:40

score 1 · Answer 1 · answered Nov 24 '15 at 09:00

use it like this with GROUP_CONCAT

SELECT i.instagrammer_id
  , CONCAT(i.first_name,' ',i.last_name) AS instagrammer_name
  , FLOOR(DATEDIFF (NOW(), i.date_of_birth)/365) AS age
  , i.date_of_birth
  , i.gender
  , i.email
  , i.created_date
  , GROUP_CONCAT(b.name_en) AS industrie_branch
FROM instagrammer AS i
LEFT JOIN industries_branches b ON FIND_IN_SET(b.industrie_branch_id, i.industrie_branch)
WHERE i.status = 1
GROUP BY i.instagrammer_id
ORDER BY instagrammer_name DESC

How to concat find_in_set results with comma separated string in mysql

Select Query:

1 Answers1