Answer: In Oracle, we can CONNECT BY clause for this.
The starting point will be the employee who does not have a manager. Below that we can connect the employee IDs with their Manager IDs and keep printing the records.
Oracle provides a pseudocolumn LEVEL that gives the level of each record in hierarchy.
Query will be as follows:
SELECT f_name, emp_id, manager_id, LEVEL
START WITH emp_id = 10
CONNECT BY PRIOR emp_id = manager_id;
F_NAME EMP_ID MANAGER_ID LEVEL
John 10 1
George 14 10 2
Jill 16 14 3
Bill 15 14 3
Jay 18 14 3
Read the full book at www.amazon.com