Search code examples
sqloracleoracle10g

How to write a SQL query to find first name of all the employees and their department's manager?

Employees Table Departments Table

These are the two tables that I need to join. To be specific, what i need to find is all employees first name and their department's manager's first name. I'm just not sure how the query will go.

Here are some details about the above two tables :

  1. Employees.DEPID = Departments.DID
  2. Departments.MGRID = It shows the Employees.EID of the person who is manager of that respective department.

This is the closest query where I got my expected result (but not quite)-

Query -

select
  EMPLOYEES.FNAME, DEPARTMENTS.D_NAME, DEPARTMENTS.MGRID
from EMPLOYEES
  left join DEPARTMENTS
  on EMPLOYEES.DEPID = DEPARTMENTS.DID;

Result -

FNAME                D_NAME          MGRID
-------------------- --------------- -----
SHAHID               Accounts
PREM                 Accounts
AKASH                Accounts
SHAAN                IT
VISHAL               IT
DEV                  Management
SOME                 Design
MANISHA              Design
ISHAAN               Web             E-01
ADITI                Web             E-01
KAVITA               Web             E-01

Now, I need to change this MGRID to First Name of the manager.

Solution

  • One option is to use self-join (i.e. use the employee table twice - once for employees, and other time for managers). Something like this:

    Sample data:

    SQL> with
  2  employees (eid, fname, depid, designation) as
  3    (select 'E-01', 'KAVITA', 'D-01', 'MANAGER'  from dual union all
  4     select 'E-02', 'ADITI' , 'D-01', 'EMPLOYEE' from dual union all
  5     select 'E-07', 'ISHAAN', 'D-01', 'EMPLOYEE' from dual
  6    ),
  7  departments (did, d_name, mgrid) as
  8    (select 'D-01', 'Web', 'E-01' from dual)

    Query:

      9  select d.d_name,
 10         e.fname employee,
 11         m.fname  manager
 12  from departments d join employees e on e.depid = d.did
 13                     join employees m on m.depid = d.did and m.eid = d.mgrid;

D_N EMPLOYEE   MANAGER
--- ---------- ----------
Web KAVITA     KAVITA
Web ADITI      KAVITA
Web ISHAAN     KAVITA

SQL>