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_IdParent_IdCommunity_Nametmr>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 Fun10 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= 0Results: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 110 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= 1Results:COMMUNITY COMMUNITY_ID PARENT_ID----------------------------------- ------------ ----------NBC 2 1 TV Entertainment 1 0ABC 3 1 TV Entertainment 1 0FOX 4 1 TV Entertainment 1 06 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 = 7Results: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
