0

I have two tables as following

Table products 

-product_id-        -categorie ids-
  1                   2,4,5,6
  2                   1,4,3
  4                    3,5

Table categories 

c-category_id-       -(catname)-
  1                  cat1
  2                  cat2
  3                  cat3
  4                  cat4
  5                  cat5
  6                  cat6

I need result in this format

-product_id-        -categories-
  1                  cat2
  1                  cat4
  1                  cat5
  1                  cat6
  2                  cat1
  2                  cat4
  .                    .
  .                    .
  .                    .
jarlh
  • 42,561
  • 8
  • 45
  • 63
Girish Patil
  • 19
  • 1
  • 6
  • 3
    Good idea to fix that! Comma separated values only cause a lot of trouble. – jarlh Mar 30 '18 at 09:45
  • You should go with this idea https://stackoverflow.com/questions/19101550/mysql-join-two-tables-with-comma-separated-values?answertab=votes#tab-top – er.irfankhan11 Mar 30 '18 at 09:55

1 Answers1

1
SELECT  a.product_id,
        b.catname
FROM    products a
        INNER JOIN category b
            ON FIND_IN_SET(b.category_id, a.categorie_ids) > 0
            ORDER BY a.product_id

Live Demo

http://sqlfiddle.com/#!9/02efca/5

Jay Shankar Gupta
  • 5,918
  • 1
  • 10
  • 27