MySQL - JOIN based on value of column?

Question

I have two tables:

Table "categories"

category  |  parents
1         |  5,4,1
2         |  3,2

Column parents is group of numbers divided with comma, so it could be used in query as IN(parents)

Table "categories_goods"

item | category
10   | 1
12   | 2

And I want to export data to third table - there will be all parents for every category. Result should be:

Table "categories_goods_all"

item | category
10   | 5
10   | 4
10   | 1
12   | 3
12   | 2

I have this solved in PHP, but it is slow when there is 10000 * x of rows in table categories_goods. So I am looking for pure MySQL solution. Any ideas?

score 1 · Accepted Answer · edited May 23 '17 at 11:54

Use this

Demo: http://rextester.com/CAF76544

Query

select
  g.item,
  SUBSTRING_INDEX(SUBSTRING_INDEX(c.parents, ',', numbers.n), ',', -1) category1
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers 
 INNER JOIN categories c 
  on CHAR_LENGTH(c.parents)
     -CHAR_LENGTH(REPLACE(c.parents, ',', ''))>=numbers.n-1
  inner join categories_goods g
  on c.category=g.category 
order by
  c.category, n

Thanks to this answer to split csv to rows.

Explanation: The number table generate number 1-5. You might want to add more rows here for if needed for more parent. With its help, you can separate csv to columns. Refer the answer URL given above for the same.

Now you just need to do a join to categories_goods to fetch item corresponding to parent

Thank you, this worked great!! :) Is there any way how to make it to parse any number of values? Now it works with 5. I can manually add select 6 union all ... but could it be infinitely somehow? — Jan Šafránek, Mar 22 '17 at 15:41

MySQL - JOIN based on value of column?

1 Answers1