Select data with a hierarchical (parent/child) relationship.

Select data with a hierarchical (parent/child) relationship.

Syntax:

SELECT...
[START WITH initial_condition]
CONNECT BY [nocycle] PRIOR recurse_condition
[ORDER SIBLINGS BY order_by_clause]

Key:

START WITH : The row(s) to be used as the root of the hierarchy

CONNECT BY : Condition that identifies the relationship between
parent and child rows of the hierarchy

NOCYCLE : Do not circle around loops (where the current row has
a child which is also its ancestor.)

ORDER SIBLINGS BY : Preserve ordering of the hierarchical query
then apply the order_by_clause to the sibling rows
Example:

create table test_connect_by (
Level1 varchar2(100),
Item varchar2(100)
);

Sample Data

LEVEL1 ITEM

0 123
1 345
2 678
1 987
0 666

Inserted above values in the table

insert into test_connect_by values(0,666);

select * from test_connect_by


LEVEL1 ITEM

0 123
1 345
2 678
1 987
0 666


select * from test_connect_by
connect by prior item=level1


LEVEL1 ITEM

0 123
0 666
1 345
1 987
2 678

=====================================================