Here is my category table.....
I want a single query that shows all above parents of particular category_id. example: If in query i define category_id=7 then result will be like that:
7 PORTABLE ELECTRONICS 6
6 ELECTRONICS 1
and parent of 1 is null then query will terminate. In short, I just want all parents of particular category_id.
Thanks in advance.
This solution is limited to 10 levels of hierarchy.
set @category := 7 ; select concat_ws (' ',c0.category_id,c1.name,c1.category_id,c2.name,c2.category_id,c3.name,c3.category_id,c4.name,c4.category_id,c5.name,c5.category_id,c6.name,c6.category_id,c7.name,c7.category_id,c8.name,c8.category_id,c9.name,c9.category_id) as hier from category as c0 left join category as c1 on c1.category_id = c0.parent left join category as c2 on c2.category_id = c1.parent left join category as c3 on c3.category_id = c2.parent left join category as c4 on c4.category_id = c3.parent left join category as c5 on c5.category_id = c4.parent left join category as c6 on c6.category_id = c5.parent left join category as c7 on c7.category_id = c6.parent left join category as c8 on c8.category_id = c7.parent left join category as c9 on c9.category_id = c8.parent where c0.category_id = @category ;