mysql - Join statement for multiple values in the same column separated by comma -

i have 2 tables follows:-

products

   product_name |  category_id     nutella      |  1,2    milk         |  3,4 

categories

  cat_id        | name      1          | dessert      2          | chocolate      3          | dairy      4          | milk 

i using datatable server side processing using ssp library display products table.

i need make join statement me categories each product since category column contains comma separated multiple values!

is there anyway can using mysql?

edit

expected output

 product_name |  category_id     nutella      |  dessert,chocolate    milk         |  diary,milk 

assuming sql server, first, can use cte rows comma separated categories ids rows, using inner join on cat_id, can desired result.

see demo sql server

;with cteproduct (product_name,category_id)  ( select a.product_name,        split.a.value('.', 'varchar(100)') category_id       (      select product_name,            cast ('<m>' + replace(category_id, ',', '</m><m>') + '</m>' xml) data         products  ) cross apply data.nodes ('/m') split(a) )   select cteproduct.category_id,cteproduct.product_name,categories.name  cteproduct       inner join categories on cteproduct.category_id = categories.cat_id 

edit:- mysql, have follow same approach, convert comma separated string multiple rows inner join. not know how separated comma separated string rows in mysql refer here. utilized same answer here.

see demo mysql

select tablename.product_name,group_concat(categories.name) name ( select product_name,substring_index(substring_index(t.category_id, ',', n.n), ',', -1) category_id   products t cross join  (    select a.n + b.n * 10 + 1 n           (select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)    ,(select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) b     order n ) n  n.n <= 1 + (length(t.category_id) - length(replace(t.category_id, ',', '')))   ) tablename   inner join categories on tablename.category_id = categories.cat_id   group tablename.product_name;