GROUP_CONCAT with LEFT JOIN condition?

Question

Assume that I have these tables:

categories

id   name           parent_id
1    Category 1     0     
2    Category 2     1
3    Category 3     1
4    Category 4     2 


products

id   name                 category_id
1    product name 1       2        
2    product name 2       2
3    product name 3       3 
4    product name 4       4


promos

id   product_ids
1    1,2,3
2    1,4
3    2,3
4    4

I want to get all data like this:

product_id   product_name       promo_ids
1            product name 1     1,2
2            product name 2     1,3
3            product name 3     1,3
4            product name 4     2,4

This is how I query the database:

SELECT GROUP_CONCAT(pr.id) as promo_ids, p.id as product_id, p.name as product_name, 
FROM `products` as p
LEFT JOIN `promos` as pr ON `p`.`id` IN (pr.product_ids)
WHERE p.category_id IN(1,2,3,4)
GROUP BY p.id

But the result is not as I expected.

product_id   product_name       promo_ids
1            product name 1     
2            product name 2     
3            product name 3     
4            product name 4

What was wrong with my query? I guess the problem is the [promos][product_ids] field but I have no idea to solve this.

Thanks in advance!

http://stackoverflow.com/questions/10956188/join-and-group-concat-with-three-tables?rq=1 — Vignesh Kumar A, Jun 10 '14 at 05:13

score 2 · Accepted Answer · answered Jun 10 '14 at 05:09

2

Storing comma separated ids is not a good idea see Database Normalization,you cannot simply use IN() to join with your set column for now you need to use FIND_IN_SET to find the values in a comma separated set

SELECT 
  GROUP_CONCAT(pr.id ORDER BY pr.id) AS promo_ids,
  p.id AS product_id,
  p.name AS product_name 
FROM
  `products` AS p 
  LEFT JOIN `promos` AS pr 
    ON FIND_IN_SET(`p`.`id`,pr.product_ids) 
WHERE p.category_id IN (1, 2, 3, 4) 
GROUP BY p.id

answered Jun 10 '14 at 05:09

M Khalid Junaid

63,861
10
90
118

Thank you so much M Khalid Junaid. Just one more question. According to the docs, FIND_IN_SET returns a value in range of 1 to N if the `id` is in the `product_ids`. What does it mean in the ON condition? – Nấm Lùn Jun 10 '14 at 05:26
2

basically, when the value is not found on the list, the function returns `0` and mysql automatically parses it to `false` since the `ON` requires boolean condition. – John Woo Jun 10 '14 at 05:29
1

@NấmLùn suppose this set (1,2,3) and i am comparing value 1 using FIND_IN_SET() with this set so result will be 1 the index position of matched value in set so for 2 result will be 2 and for 4 result will be 0 – M Khalid Junaid Jun 10 '14 at 05:29

GROUP_CONCAT with LEFT JOIN condition?

1 Answers1