Use Of 'Connect By' clause for Hierarchal Query

The Connect By clause is useful for tables which have 2 or more columns with parent-child relationships. While displaying rows, we can use 'LEVEL' pseudo column to indent the results as hierarchy.

Consider the following example of a community table:

 
Community_Id
Parent_Id
Community_Name

tmr>Select * from Communities;

COMMUNITY_ID  PARENT_ID COMMUNITY_NAME
------------ ---------- -------------------------
           1          0 TV Entertainment
           2          1 NBC
           3          1 ABC
           4          1 FOX
           5          2 Sports
           6          2 Movies
           7          3 Talk Show
           8          3 Documentary
           9          7 Educational
          10          7 Fun

10 rows selected.

/* Find all children, all leaf lets From Root*/
 SELECT LPAD(' ',3*(LEVEL-1)) || substr(Community_Name, 1, 30) Community ,
         Community_id, Parent_Id
 FROM   Communities
 CONNECT BY PRIOR Community_id = Parent_Id
 START WITH Parent_id= 0

Results:

COMMUNITY                           COMMUNITY_ID  PARENT_ID
----------------------------------- ------------ ----------
TV Entertainment                               1          0
   NBC                                         2          1
      Sports                                   5          2
      Movies                                   6          2
   ABC                                         3          1
      Talk Show                                7          3
         Educational                           9          7
         Fun                                  10          7
      Documentary                              8          3
   FOX                                         4          1

10 rows selected.

/* Find immediate children For Parent_Id = 1 : Just go 1 level Down  */
 SELECT LPAD(' ',3*(LEVEL-1)) || substr(Community_Name, 1, 30) Community ,
         Community_id, Parent_Id
 FROM   Communities
 CONNECT BY Community_Id = PRIOR  Parent_Id
 START WITH Parent_Id= 1

Results:

COMMUNITY                           COMMUNITY_ID  PARENT_ID
----------------------------------- ------------ ----------
NBC                                            2          1
   TV Entertainment                            1          0
ABC                                            3          1
   TV Entertainment                            1          0
FOX                                            4          1
   TV Entertainment                            1          0

6 rows selected.


/* Start At Node Community_Id = 7: Traverse up all the way till root */
 SELECT LPAD(' ',3*(LEVEL-1)) || substr(Community_Name, 1, 30) Community ,
         Community_id, Parent_Id
 FROM   Communities
 CONNECT BY Community_id = PRIOR  Parent_Id
 START WITH Community_id = 7

Results:

COMMUNITY                           COMMUNITY_ID  PARENT_ID
----------------------------------- ------------ ----------
Talk Show                                      7          3
   ABC                                         3          1
      TV Entertainment                         1          0


/* Find all children, upto some LEVEL */
 SELECT LPAD(' ',3*(LEVEL-1)) || substr(Community_Name, 1, 30) Community ,
         Community_id, Parent_Id
 FROM   Communities
 Where  LEVEL <= 2
 CONNECT BY PRIOR Community_Id = Parent_Id
 START WITH Parent_Id= 0

Results:

 
COMMUNITY                           COMMUNITY_ID  PARENT_ID
----------------------------------- ------------ ----------
TV Entertainment                               1          0
   NBC                                         2          1
   ABC                                         3          1
   FOX                                         4          1

Trupti Rajparia

Submit a Tip

Browse "Java" Tips

Browse All Tips

0 Comments (click to add your comment)

Comment and Contribute