Join statement for multiple values in the same column separated by comma

Question

I have 2 tables as follows:-

Products

   Product_name |  category_id

   Nutella      |  1,2
   Milk         |  3,4

Categories

  cat_id        | name
     1          | dessert
     2          | chocolate
     3          | dairy
     4          | milk

I am using datatable with server side processing using SSP library to display the products table.

I need to make join statement that get me the categories for each product since the category column contains comma separated multiple values!

Is there anyway I can do it using MYSQL?

EDIT

Expected output

 Product_name |  category_id

   Nutella      |  dessert,chocolate
   Milk         |  diary,milk

which RDBMS you are using? – HaveNoDisplayName Jul 23 '15 at 15:12 — HaveNoDisplayName, Jul 23 '15 at 15:12

score 2 · Answer 1 · edited May 23 '17 at 12:21

Assuming as SQL Server, First, You can use the CTE to get rows from comma separated categories ids into rows, then using INNER JOIN on cat_id, you can get the desired result.

SEE DEMO SQL Server

;WITH CTEProduct (Product_name,category_id) 
AS
(
SELECT A.Product_name,  
     Split.a.value('.', 'VARCHAR(100)') AS category_id  
 FROM  
 (
     SELECT Product_name,  
         CAST ('<M>' + REPLACE(category_id, ',', '</M><M>') + '</M>' AS XML) AS Data  
     FROM  Products
 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
) 

Select CTEProduct.category_id,CTEProduct.Product_name,Categories.name 
From CTEProduct 
     INNER JOIN Categories ON CTEProduct.category_id = Categories.cat_id

EDIT:- For MYSQL, we have to follow the same approach, convert comma separated string to multiple rows then INNER JOIN. I does not know much about How to separated comma separated string to rows in MYSQL so Refer Here. I utilized the same answer here.

SEE DEMO MYSQL

Select tablename.Product_name,GROUP_CONCAT(Categories.name) name
From (
SELECT Product_name,SUBSTRING_INDEX(SUBSTRING_INDEX(t.category_id, ',', n.n), ',', -1) category_id
  FROM Products t CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
 WHERE n.n <= 1 + (LENGTH(t.category_id) - LENGTH(REPLACE(t.category_id, ',', '')))
  ) tablename
  INNER JOIN Categories ON tablename.category_id = Categories.cat_id
  GROUP BY tablename.Product_name;

Are you sure, you specify previously in your question that you are using MYSQL? — HaveNoDisplayName, Jul 23 '15 at 15:32
Actually I update the tags after I saw your response, sorry for that. Thanks so much for your help but is there any way to get the return values for each product in one row? — palAlaa, Jul 23 '15 at 16:10
Update your question with expected output, then I have better idea. — HaveNoDisplayName, Jul 23 '15 at 16:13
do you mean your query? It's so difficult for me to understand and update — palAlaa, Jul 23 '15 at 16:30

Join statement for multiple values in the same column separated by comma

1 Answers1