So I have a menu table with a parent child relationship menu_id, menu_name, parent_id and I want to retrieve the whole branch that Menu Child E is in. ie all its ancestors. The result should be ordered from the root.
So given:
I want:
select
m.menu_id, m.menu_name, m.parent_id
from
MENU m
start with
m.menu_id = 5
connect by
prior m.parent_id = m.menu_id
ORDER BY LEVEL DESC;
So given:
Root (id:0 parent_id: null) Menu Parent A(id:1 parent_id: 0) Menu Child B(id:2 parent_id: 1) Menu Parent C(id:3 parent_id: 0) Menu Child D(id:4 parent_id: 3) Menu Child E(id:5 parent_id: 3)
I want:
Root (id:0 parent_id: null) Menu Parent C(id:3 parent_id: 0) Menu Child E(id:5 parent_id: 3)
select
m.menu_id, m.menu_name, m.parent_id
from
MENU m
start with
m.menu_id = 5
connect by
prior m.parent_id = m.menu_id
ORDER BY LEVEL DESC;
Comments
Post a Comment