Monday, October 11, 2010

Traversing a hierarchical oracle tree in SQL backwards

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:
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;

No comments:

Post a Comment