| Hierarchical data in SQL [message #375039] |
Fri, 20 July 2001 06:06  |
Jacqlyn Edge
Messages: 2
Registered: July 2001
|
Junior Member |
|
|
Say I have a table, representing a hierarchical structure, with the following fields:
id - unique id for the entry
name - entry's name (and other flat details)
parent_id - identifier indicating the parent entry of this one (keys back into this table)
And I need to get the full set of 'final child' entries (i.e. those at the lowest level) belonging to a particular parent, where the number of levels in the hierarchy is variable. So, for instance, if I have the following data:
id name parent_id
-----------------------
1 a NULL - the top level entry
2 b 1 - a child of 1
3 c 1
4 d 2 - a child of 2
5 e 2
6 f 3
7 g 3
8 h 4
9 i 8
10 j 9
where the structure looks like this:
1 - top parent level
2,3 - children of 1
4,5 - children of 2
6,7 - children of 3
8 - child of 4
9 - child of 8
10 - child of 9
and I need to return the data only for 3,5,7,10 (i.e. all those which have no children themselves).
I assume I need to use self-joins to get this data, but that may only be possible for a fixed number of levels? or is there a way to keep joining (in one query) until there are no more levels to expand?
|
|
|
|
|
|
| Re: Hierarchical data in SQL [message #375056 is a reply to message #375047] |
Sun, 22 July 2001 22:37  |
Jacqlyn Edge
Messages: 2
Registered: July 2001
|
Junior Member |
|
|
I apologise for not making my query clear. Thanks for the advice: that will certainly work for extracting just the final child information. What I need (and forgot to mention) is the actual hierarchy as well, so instead of just returning the children, it will need to retrun all the child nodes in the path, so for instance, 2/4/8/9/10 or 1/3/6. Which suggests some cunning trick with self-joins ...?
Thanks again for the help!
:)
jacqlyn
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
