1.11M Members

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

 
0
 

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

 
0
 

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
 
0
 

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

 
1
 

Have a look at the FIND_IN_SET function (MySQL).

 
0
 

@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
 
0
 

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

 
0
 

nice informative stuff

Isn't it about time forums rewarded their contributors?

Earn rewards points for helping others. Gain kudos. Cash out. Get better answers yourself.

It's as simple as contributing editorial or replying to discussions labeled or OP Kudos

You
This article has been dead for over six months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article