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?
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments