Ask the Expert

Showing inheritance, Part 2a

Continued from Part 1...

    Requires Free Membership to View

2. Bottom-Up Hierarchical Query. Most of the hierarchical queries (START WITH ... CONNECT BY ... PRIOR) I've seen are top-down queries, that is, they start with parents and then show children. One of the properties of top-down queries is that no row in the table will appear twice (unless the START WITH set includes ancestors and their descendants). This is a corollary of the condition that no row has two parents. That's not what you want here: you want to show A once in its own right, a second time as the parent of B, again as the ancestor of C, yet again as the ancestor of D. To do this you need a bottom-up query, where you start with children and then show their ancestors. What determines if a hierarchical query is top-down or bottom-up? The placement of the PRIOR operator. Where top-down queries say something like "CONNECT BY parent_id = PRIOR id", bottom-up queries say "CONNECT BY id = PRIOR parent_id".

SELECT  LEVEL,
        type
FROM    parent_child
CONNECT BY
        type    = PRIOR parent;
 
     LEVEL TYPE
---------- ----------
         1 A
         1 B
         2 A
         1 C
         2 B
         3 A
         1 D
         2 B
         3 A
This shows each type and its ancestors. To get the properties, simply JOIN with the property view.
SELECT  LEVEL,
        type,
        p.property
FROM    parent_child     pc   JOIN
        property         p    USING (type)
CONNECT BY
        type    = PRIOR pc.parent;

     LEVEL TYPE         PROPERTY
---------- ---------- ----------
         1 A                   1
         1 A                   2
         1 B                   3
         2 A                   1
         2 A                   2
         1 B                   4
         2 A                   1
         2 A                   2
         1 C                   5
         2 B                   3
         3 A                   1
         3 A                   2
         2 B                   4
         3 A                   1
         3 A                   2
         1 D                   6
         2 B                   3
         3 A                   1
         3 A                   2
         2 B                   4
         3 A                   1
         3 A                   2
This is nothing other than the desired result set. You don't recognize it? That's because it needs a few changes.
  1. There are 22 rows when there should be 16. Apparently, SQL is doing the JOIN before the CONNECT BY.
  2. The order should be the same as the order in the previous result set
  3. The first column should show the starting type
  4. The second column should be NULL when it's the starting type

Part 2 is continued...


This was first published in August 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: