Hierarchical Query: Get the direct children count for each node

570 views Asked by At

I am new to hierarchical queries and I have gone through most of the posts on it.

I have a query that i would like to improve.

My Scenario is something like this:

Sample
-------
ID,  
NAME,  
PARENT_ID  

Table Data as follows:

ID    NAME   PARENT_ID  
-----------------------
1     A    (null)  
2     B    1    
3     C    2    
4     D    1    

I want the result to be something like this:

ID    NAME    PARENT_ID    IS_LEAF    LEVEL   DIRECT_CHILDREN_COUNT  
-------------------------------------------------------------------
1      A        (null)       0         1      2
2      B        1            0         2      1
3      C        2            1         3      0
4      D        1            1         2      0

I wrote the following query which gives all the columns except the DIRECT_CHILDREN_COUNT

SELECT ID, NAME, PARENT_ID, CONNECT_BY_LEAF AS IS_LEAF, LEVEL FROM SAMPLE 
START WITH PARENT_ID = null CONNECT BY PRIOR ID = PARENT_ID

There are many posts on how to get the count of all the children of the node(not just the direct children), Those are not applicable to me because

1.I only want the direct children count.
2.If possible I want to achieve this without using joins.

I have no clue how to get the direct children count as a column value for each of the records. Is there anyway I could get the DIRECT_CHILDREN_COUNT using the hierarchical queries?

0

There are 0 answers