Re: Multi Level Tree Structure

bpinaki_at_gmail.com 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

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. Received on Fri Sep 09 2005 - 08:19:38 CDT