group concat - Mysql working with comma separated list - Junction table -

i have junction table productid , accessory column:

table1

productid    accessory 1            2 1            3   2            1 2            4 2            5 3 4            1 5            2 

it means productid 2, has accessory productids 1,4 , 5 ...

and have table 2 below grp , productid provided, need fetch accesories.

table2

grp     productid     accessories        2              b       3              c       1              d       4              e       5  

so if using update this

table2

update table2 t2 set t2.accessories = (select group_concat(distinct t1.accessory) table1 t1                       t1.productid = t2.productid)  grp     productid     accessories       2             1,4,5 b       3               c       1             2,3 d       4             1 e       5             2 

but want change productids in t2.accessories grp character instead according t2.productid final table looks .

table2

grp     productid     accessories       2             c,d,e b       3               c       1             a,b d       4             c e       5             

here tables http://sqlfiddle.com/#!9/83ec9

this should work you:

update table2 t set t.accessories = ifnull((select foo                         (select t1.productid,group_concat(t2.grp) foo                             table1 t1                              left join table2 t2 on t1.accessory = t2.productid                              group t1.productid                         ) bar productid = t.productid                      ),'') 

here updated sql fiddle