Using CONNECT BY PRIOR along with a select statement, we could retrieve data in a hierarchical manner

If we have to query the employees reporting to a Manager James, directly, we do

SELECT EMPNAME, EMP_ID, MANAGER

FROM EMPLOYEE

WHERE  MANAGER= James 

EMPNAME                            EMP_ID        MANAGER

Bob                              3257                 James

Stacy                            9968                 James

Richy                            3689                 James

Richard                        4204                 James

Jones                            1147                 James

But how to find all employees who report to the Manager James, directly and indirectly?

We could use the CONNECT BY PRIOR facility in Oracle.

Thus goes the query:

SELECT EMPNAME, MANAGER, LEVEL

FROM EMPLOYEE

START WITH MANAGER= James 

CONNECT BY PRIOR EMPNAME = MANAGER

Explanation:

START WITH   Specifies the root of the hierarchy, i.e. where to start from.

Here, MANAGER= James  gives the starting point for the hierarchical query.

CONNECT BY PRIOR  explains the relationship between the parent and the child.

It connects to the next level using EMPNAME = MANAGER. i.e. it connects to the next level Employee, to whom

 EMPNAME  is  MANAGER  to. Thus it goes down the levels  recursively.

PRIOR   This is used to achieve the recursive condition.

Now, what if you want to find all Direct and Indirect managers of James?

We just need to reverse the direction of the query. And it s simple.

Just change the sides of EMPNAME and MANAGER. That s it.

SELECT EMPNAME, MANAGER, LEVEL

FROM EMPLOYEE

START WITH MANAGER= James 

CONNECT BY PRIOR MANAGER= EMPNAME

Note: Here the Level starts from James, since he is the root. This Level only indicates the relative position of others to the Root.

This has nothing to do with the Level of a Manager or an employee.