Path: utzoo!news-server.csri.toronto.edu!cs.utexas.edu!wuarchive!rex!uflorida!novavax!rm1!bapat
From: bapat@rm1.UUCP (Subodh Bapat)
Newsgroups: comp.databases
Subject: Traversing Tree Structures Using SQL
Message-ID: <1024@rm1.UUCP>
Date: 14 Mar 91 22:15:46 GMT
Organization: (I don't speak for) Racal-Milgo, Ft Lauderdale, FL
Lines: 51



I'm wondering if there's anyone out there who might have some tricks in
their bag of SQL queries that allows the traversal of a tree-structured
hierarchy. (This is probably a FAQ but I haven't seen any FAQ list for
this group).

I have a data structure that looks like:

                                A
                               / \
                             /     \
                            B       C
                           / \        \
                         /     \        \
                        D       E        F

which is flattened into the tabular relation TREE as:

                            TREE
                 Childnode        Parentnode
                ------------------------------
                     A                NULL
                     B                 A
                     D                 B
                     E                 B
                     C                 A
                     F                 C
           < plenty of other records in this table for
             other unrelated trees >

Is it possible to specify a single SQL query which says "Start with A, and 
obtain records for all nodes which are descendants of A" ?

My research so far indicates that Oracle is the only RDBMS which supports
the CONNECT BY clause, which allows this to happen fairly easily. However,
the CONNECT BY clause is not supported by most other RDBMS vendors, nor is
it part of ANSI X3H2 SQL. I'm looking for a way to do this using standard
ANSI SQL. I tried a few queries using table aliasing but none of them worked
right.

Oracle also returns the pseudo-column LEVEL for records returned from
CONNECT-BY queries, indicating the depth of the traversed node in the 
subtree. It would be nice if one could return a similar indication using a 
standard ANSI SQL query.

Looking forward to tips from anyone who might have done this before,

-- 
Subodh Bapat              bapat@rm1.uu.net     OR           ...uunet!rm1!bapat
                          Racal-Milgo, Ft Lauderdale