Nitin Sonawane Nitin Sonawane - 1 year ago 66
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:



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 Source

This solution is limited to 10 levels of hierarchy.

set @category := 7

select      concat_ws (' ',c0.category_id,,c1.category_id,,c2.category_id,,c3.category_id,,c4.category_id,,c5.category_id,,c6.category_id,,c7.category_id,,c8.category_id,,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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download