Re: How to find out hierchy of employee?

["Derrick Betts" <derrick(at)blueaxis(dot)com>]

This is more a matter of table structure and the changing of the way you think about how the employees, managers, etc. relate to each other in the database table.  I've modified your table below by adding a StationID column:

     EMPNO  ENAME      JOB              StationID     MGR           HIREDATE          SAL        COMM       DEPTNO
    ----------     ----------       ---------            --------------   ----------          ---------------           ----------    ----------        ----------
      7369     SMITH        CLERK           B01A1A     7902             17-DEC-80            800                            20
      7499     ALLEN       SALESMAN    B01B1       7698             20-FEB-81           1600        300               30
      7521     WARD       SALESMAN    B01B2       7698             22-FEB-81           1250        500               30
      7566     JONES       MANAGER     B01A         7839             02-APR-81           2975                            20
      7654     MARTIN     SALESMAN    B01B3       7698             28-SEP-81           1250       1400              30
      7698     BLAKE      MANAGER      B01B         7839             01-MAY-81           2850                            30
      7782     CLARK      MANAGER      B01C         7839             09-JUN-81           2450                            10
      7788     SCOTT      ANALYST        B01A2       7566             19-APR-87           3000                            20
      7839     KING         PRESIDENT    B01                               17-NOV-81           5000                            10
      7844     TURNER    SALESMAN    B01B4       7698             08-SEP-81           1500          0                 30
      7876     ADAMS      CLERK           B01A2A    7788             23-MAY-87           1100                            20
      7900     JAMES      CLERK           B01B5       7698             03-DEC-81            950                            30
      7902     FORD        ANALYST        B01A1      7566             03-DEC-81           3000                            20
      7934     MILLER     CLERK            B01C1       7782             23-JAN-82           1300                            10

 

You can now delete the MGR column, as it is not needed.  The StationID column tells you who each employees manager, Sr. Manager, and Administrator is by looking at the StationID. The previous query I sent allows you to select the president, Sr. Manager, Manager, etc. for the specific employee in question using the LIKE clause against the employee StationID.

 

Hope this helps.

Derrick Betts