tree structure oracle sql

Question

I would like to show employees hierarchy in tree structure.

I have this table.

Employee
-------------
EMP_ID (Number)
JOB_ID (Number)
Manager_ID (Number)
Effective_from (Date)
Effective_to (Date)

EMP_ID represents the Manager employee has. I would like to show the whole tree structure of Managers.

Table for reference.

EMP_FIRST_NAME  JOB_DESCRIPTION      MANAGER    EFFECTIVE_F EFFECTIVE_T
--------------- -------------------- ---------- ----------- -----------
Tomm            General Manager                 01-jan-2000 01-jan-3000
Mohammed        Senior Accountant    Tomm       01-jan-2000 01-jan-3000
Ali             Accountant           Tomm       01-jan-2000 01-jan-3000
Basel           Accountant           Mohammed   01-jan-2000 01-jan-3000

score 0 · Answer 1 · answered Aug 30 '22 at 08:00

This is a classic hierarchical query; demonstrated on Scott's EMP table:

SQL> select * From employee;

    EMP_ID EMPLOYEE_N JOB       MANAGER_ID
---------- ---------- --------- ----------
      7369 SMITH      CLERK           7902
      7499 ALLEN      SALESMAN        7698
      7521 WARD       SALESMAN        7698
      7566 JONES      MANAGER         7839
      7654 MARTIN     SALESMAN        7698
      7698 BLAKE      MANAGER         7839
      7782 CLARK      MANAGER         7839
      7788 SCOTT      ANALYST         7566
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN        7698
      7876 ADAMS      CLERK           7788
      7900 JAMES      CLERK           7698
      7902 FORD       ANALYST         7566
      7934 MILLER     CLERK           7782

14 rows selected.

You'd then

SQL> select lpad(employee_name, 2 * (level + 1), ' ') name
  2  from employee e
  3  start with manager_id is null
  4  connect by prior emp_id = manager_id;

NAME
--------------------------------------------------------------------------------
KING
 JONES
   SCOTT
     ADAMS
    FORD
     SMITH
 BLAKE
   ALLEN
    WARD
  MARTIN
  TURNER
   JAMES
 CLARK
  MILLER

14 rows selected.

SQL>

tree structure oracle sql

1 Answers1