Nitin Sonawane Nitin Sonawane - 1 month ago 8
MySQL Question

How to get all parent note of particular category-id from given sql table?

Here is my category table.....

enter image description here

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.

Answer

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
;