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

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 :

Employees.DEPID = Departments.DID
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.

CodePudding user response：

LEFT JOIN the employees table a second time (and give it a different alias) correlating between the department's manager ID and the manager's employee ID and then get the manager's name from that second instance of the employee table.

SELECT e.FNAME,
       d.D_NAME,
       m.FNAME AS manager_name
FROM   employees e
       LEFT OUTER JOIN departments d
       ON e.DEPID = d.DID
       LEFT OUTER JOIN employees m
       ON d.mgrid = e.eid;

CodePudding user response：

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>