Querying hierarchical relationships between Oracle DB records...

The start with .. connect by clause make us to select data which has a hierarchical relationship.
For example in  Country -> city -> street or Manager -> Employee relations...

Hierarchical Data Structure

PositionsTable

This clause runs recursively, starts with a specified point and returns all the chain of records ...

Query:

SELECT * FROM POSITIONS

START WITH ID = 9

CONNECT BY PRIOR MANAGER_ID =ID;

Result Of The Query:

ID POSITION                        MANAGER_ID

---  -------------------------------    ----------------------

9   DOCUMENTER                 4

4   IT TEAM LEADER             3

3   PROJECT MANAGER       1

1   SOFTWARE DIRECTOR   0