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.
- There are 22 rows when there should be 16. Apparently, SQL is doing the JOIN before the CONNECT BY.
- The order should be the same as the order in the previous result set
- The first column should show the starting type
- The second column should be NULL when it's the starting type
Part 2 is continued...
This was first published in August 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation