How to join two tables using a comma-separated-list

[muralibobby2015]

Hello,

I have products table like below

Table products {
-product_id-        -categories-
  1                   2,4,5,6
  2                   1,4,3
  4                    3,5
}





Table categories {
-category_id-       -en_label(catname)-
  1                  cat1
  2                  cat2
  3                  cat3
  4                  cat4
  5                  cat5
  6                  cat6
}

i want to display categorynames using sigle query

productid   en_label

1            cat2,cat4,cat5,cat6
2            cat1,cat4,cat3

i wrote for this

SELECT m.product_id as product_id, m.product_en as product_en
  , CONCAT(c1.en_label, if(c2.en_label IS NULL,'',', '), ifnull(c2.en_label,'')) as categories 
FROM tbl_products m
LEFT JOIN tbl_categories c2 ON 
 (replace(substring(substring_index(m.cat_id, ',', 2),
  length(substring_index(m.cat_id, ',', 2 - 1)) + 1), ',', '') = c2.category_id)
INNER JOIN tbl_categories c1 ON 
 (replace(substring(substring_index(m.cat_id, ',', 1), 
  length(substring_index(m.cat_id, ',', 1- 1)) + 1), ',', '') = c1.category_id)

it s displaying only for two lists like(1,4). but there are more than cat ids are i list it is not showing. can anybody tell me how to write mysql query for this using joins

Thanks,
Murali

[bops]

This would be a much simpler task if you had another table that joins your products to categories i.e.

productstocategories

productid   categoryid
1           2
1           4
1           5
1           6
2           1
2           4
2           3
4           3
4           5

[muralibobby2015]

yes ofcourse mate! but it is already developed website. so based on existing database we have to write this query.

[pritaeas]

Have a look at the FIND_IN_SET function (MySQL).

[muralibobby2015]

@pritaeas

Thanks for your reply.

as you said i wrote an sql query but it is displaying only first row of the table with correct result but not all rows

SELECT `m`.`product_id` , `m`.`product_en` , `m`.`product_de` , GROUP_CONCAT( `c`.`en_label`
SEPARATOR "," ) AS `cats`
FROM `tbl_products` AS `m` , `tbl_categories` AS `c`
WHERE FIND_IN_SET( `c`.`category_id` , `m`.`cat_id` ) !=0

GROUP BY `m`.`product_id`

i tried using product_id=1 like

SELECT `m`.`product_id` , `m`.`product_en` , `m`.`product_de` , GROUP_CONCAT( `c`.`en_label`
SEPARATOR "," ) AS `cats`
FROM `tbl_products` AS `m` , `tbl_categories` AS `c`
WHERE FIND_IN_SET( `c`.`category_id` , `m`.`cat_id` ) !=0
AND `m`.`product_id` =1
GROUP BY `m`.`product_id`

it is displaying correct results like

product_id    product_en        product_de             cats
  1             fat frozen      tiefgefroren      Vegetables,Fruits,Frozen Food,Meat

but if i use product_id=2(other than 1-first row id) it is not displaying correctly

SELECT `m`.`product_id` , `m`.`product_en` , `m`.`product_de` , GROUP_CONCAT( `c`.`en_label`
SEPARATOR "," ) AS `cats`
FROM `tbl_products` AS `m` , `tbl_categories` AS `c`
WHERE FIND_IN_SET( `c`.`category_id` , `m`.`cat_id` ) !=0
AND `m`.`product_id` =3
GROUP BY `m`.`product_id`

but here it is not displaying comma separated vales



product_id    product_en          product_de                 cats
3              xxxxx                   yyyyyy             Vegetables

[pritaeas]

If I can, I'll try to reproduce this later.

[hira08]

nice informative stuff