Re: Multi Level Tree Structure
- From: stephen.odonnell@xxxxxxxxx
- Date: 9 Sep 2005 06:19:38 -0700
bpinaki@xxxxxxxxx wrote:
> Hello,
> I am using Oracle 8.1.7 On Windows...
>
>
> Id (Primary Key) Name (Name of the person) PredId (Predecessor Id)
> 1 A 0
> 2 B 1
> 3 C 1
> 4 D 2
> 5 E 2
> 6 F 2
> 7 G 4
> 8 H 4
> 9 I 3
> 10 J 3
>
>
> It is the problem of that Multi Level Tree structure:
>
> The table contains the tree information. The PredId of a member
> contains the Id of the Parent Node. This tree can go to any level.
>
> The only way to know that a particular node (say E, F, G, H, I, J) has
> no child is that, the IdD of that particular node will not appear in
> the PredId column. Eg. Id of G is 7. Since it has no child its ID will
> never come in the PredId column. If it comes that implies it has a
> child node.
>
> If the user puts an ID say 2(i.e level B)....all the name and ID which
> are at a level lower to B and which do not have any branches will have
> to be displayed. that is all the end nodes below are to be displayed
>
> If the user gives 2(which is B) as input...(E,F,G,H) along with thier
> id's should be displayed.
>
> Important: D should not be displayed (as it is not the end node, it has
> further branches). Node B also need not be displayed (display only the
> child nodes).
>
>
> Thanks in advance...
>
> Pinaki
I think what you want is a 'connect by' query:-
SQL> select * from test2;
ID NAME
PARENT
---------- --------------------------------------------------
----------
1 A
0
2 B
1
3 C
1
4 D
2
5 E
2
6 F
2
7 G
4
8 H
4
9 I
3
10 J
3
10 rows selected.
This query will 'walk the tree' for you:-
select level mylevel, id, name
from test2
connect by prior id = parent
start with id = 2
MYLEVEL ID NAME
---------- ----------
--------------------------------------------------
1 2 B
2 4 D
3 7 G
3 8 H
2 5 E
2 6 F
6 rows selected.
ie for id 2, it will find all its children, its children's children and
so on. The parent will have a level of 1, a child a level of 2, the
child of a child a level of 3 and so on.
To just get the immediate children just add a where level = 2 to the
query:-
select level mylevel, id, name
from test2
where level =2
connect by prior id = parent
start with id = 2
MYLEVEL ID NAME
---------- ----------
--------------------------------------------------
2 4 D
2 5 E
2 6 F
You stated that you didn't want 'D' displayed, because it has further
children - I though this was a bit strange as it is a child of B? Not
displaying the children of D could be sensible however.
Right now, I cannot think of a sensible way of excluding D - sorry!
Stephen.
.
- References:
- Multi Level Tree Structure
- From: bpinaki
- Multi Level Tree Structure
- Prev by Date: Re: 8.1.7 shutdown
- Next by Date: Re: How to uninstall Oracle8 (Server)
- Previous by thread: Multi Level Tree Structure
- Next by thread: Re: Multi Level Tree Structure
- Index(es):
Relevant Pages
|
