Thread: How to find out hierchy of employee?

How to find out hierchy of employee?

From
"VivekanandaSwamy R."
Date:
Hai,
I have proper knowledge in Oracle 9i as wel as PostgreSQL.
I have written a query in Oracle-SQL like the following...
 
select level,substr(lpad(' ',level*2)||ename,1,20) name from emp
start with ename='KING'
connect by prior empno=MGR;
/
LEVEL                NAME
----------                -----------
1                        KING
2                            JONES
3                                SCOTT                               
4                                    ADAMS
3                                FORD
4                                    SMITH
2                            BLAKE
3                                ALLEN
3                                WARD
3                                MARTIN
 
Now, i need same result in PostgreSQL.Is there any possiblity to find out the heirchy of employees?
 
 
 

Thanks  &  Regards

Vivekananda.R | Software Engineer           

Infinite Computer Solutions | Exciting Times…Infinite Possibilities...

SEI-CMMI level 5 | ISO 9001:2000

 Tel +91-80-5193-0000| Fax  +91-80-51930009 | Cell No  +91-9986463365|www.infics.com          

 
Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at info.in@infics.com and delete this email from your records.

Re: How to find out hierchy of employee?

From
"Derrick Betts"
Date:
In looking at your result set, it appears that there is no way of determining which employee is responsible to which manager. This is how I've done it:
    Every employee is assigned a StationID.  For example a Sr. Manager or Administrator is assigned an ID of 'B01'  (Business unit 01). 
    Then every manager under that Sr. Manager is assigned a StationID that ties them to the Sr. Manager.  For Example 'B01A', or 'B01B' ... 'B01ZZ'.
    Then every employee under that Manager is assigned a StationID that ties them to their specific Manager.  For example 'B01A1' or 'B01A2'.  Or for a different Manager 'B01ZZ1' or 'B01ZZ2'.
 
Now you can do a query to select all employees under a Sr. Manager, Manager, or etc. by performing this type of simple Query:
    "SELECT employee_name WHERE stationid LIKE 'B01%';"  This will return all the employees whose StationID matches that of the Sr. Manager. If you only want the employees under a Manager, just change the LIKE clause in the query to: LIKE 'B01ZZ%'
 
Hope this helps.
Derrick Betts
----- Original Message -----
Sent: Thursday, June 22, 2006 3:06 AM
Subject: [pgadmin-support] How to find out hierchy of employee?

Hai,
I have proper knowledge in Oracle 9i as wel as PostgreSQL.
I have written a query in Oracle-SQL like the following...
 
select level,substr(lpad(' ',level*2)||ename,1,20) name from emp
start with ename='KING'
connect by prior empno=MGR;
/
LEVEL                NAME
----------                -----------
1                        KING
2                            JONES
3                                SCOTT                               
4                                    ADAMS
3                                FORD
4                                    SMITH
2                            BLAKE
3                                ALLEN
3                                WARD
3                                MARTIN
 
Now, i need same result in PostgreSQL.Is there any possiblity to find out the heirchy of employees?
 
 
 

Thanks  &  Regards

Vivekananda.R | Software Engineer           

Infinite Computer Solutions | Exciting Times…Infinite Possibilities...

SEI-CMMI level 5 | ISO 9001:2000

 Tel +91-80-5193-0000| Fax  +91-80-51930009 | Cell No  +91-9986463365|www.infics.com          

 
Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at info.in@infics.com and delete this email from your records.

Re: How to find out hierchy of employee?

From
"Derrick Betts"
Date:
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