[Q] Hierarchical Data Query

From: Bradley G. Smith <bgsmith/r6pnw_deschutes_at_fs.fed.xxus>
Date: Thu, 22 Jul 1999 15:14:01 -0700 (PDT)
Message-ID: <otfzvguecajqrfpuhgrfsfsrqhf.ffadfd0.pminews@news.newsguy.com>

I have a table which contains data with parent child relationships. Each record has:

CN Parent_CN

The "root" of each tree contained the table is the record with a "Parent_CN is null" condition. I would like to create a view that has:

CN Parent_CN Root_CN

where Root_CN is the CN number of the root to which CN belongs. Each CN belongs to 1 and 1 only tree. This view allows a reference to CN to quickly find the root of the tree it belongs to.

The START WITH & CONNECT BY PRIOR clauses of Oracle allow a parent to find all children, but does it also allow a child to find it's root parent? I believe the traversal is only in one direction.

What is the solution to this problem? I would appreciate all help.

Thanks,

Brad Received on Thu Jul 22 1999 - 17:14:01 CDT