Re: Inverse hierarchical queries

Yves DEBIZET schrieb:

> The "select ;; start with ... connect by ..." is a way to describe a
> hierarchy starting down from a point (root to leaves).
>
> But how to find all the parents of a node in a tree (leaf to root) in a
> single SQL statement ?
>
> Thank you.
>

If you have a table with hierachical data like this

objectid        number not null
parentid        number not null

and parentid is a reference to objectid, then tho following statement searches from root to leaves:

select *
from hier_data
start with objectid = 1 /* this shall be the id of the root-item */ connect by prior objectid = parentid

for a search from leave to root you have to 'turn around' the statement

select *
from hier_data
start with objectid = 1357 /* this shall be the id from the leave-item */
connect by prior parentid = objectid

If you have no cycles in your hierarchy, this should work.

Let me know, if this helped you

Regards, Stephan

--
---------------------------------------------------------------
Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-17          | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------