1,105,221 Community Members

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

Member Avatar
muralikalpana
Posting Pro
549 posts since Sep 2009
Reputation Points: 17 [?]
Q&As Helped to Solve: 36 [?]
Skill Endorsements: 0 [?]
 
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

Member Avatar
bops
Posting Whiz
301 posts since Aug 2005
Reputation Points: 1 [?]
Q&As Helped to Solve: 17 [?]
Skill Endorsements: 0 [?]
 
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
Member Avatar
muralikalpana
Posting Pro
549 posts since Sep 2009
Reputation Points: 17 [?]
Q&As Helped to Solve: 36 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
pritaeas
mod_pritaeas
11,288 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,832 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
1
 

Have a look at the FIND_IN_SET function (MySQL).

Member Avatar
muralikalpana
Posting Pro
549 posts since Sep 2009
Reputation Points: 17 [?]
Q&As Helped to Solve: 36 [?]
Skill Endorsements: 0 [?]
 
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
Member Avatar
pritaeas
mod_pritaeas
11,288 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,832 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
0
 

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

Member Avatar
hira08
Light Poster
45 posts since Oct 2012
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

nice informative stuff

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