SQL join manager from same table onto a row with their employees

To start, here’s a dummy table I’ve made to show the data I’m working with:

employee title division email
Boss Person boss o bp@email
John Smith supervisor a jos@email
Jane Smith supervisor b jas@email
Leo Messi employee a lm@email
Amanda Kessel employee a ak@email
Derek Jeter employee b dj@email

I want to end up with the following info:

employee title division email supervisor_name supervisor_email
Boss Person boss o bp@email NULL NULL
John Smith supervisor a jos@email Boss Person bp@email
Jane Smith supervisor b jas@email Boss Person bp@email
Leo Messi employee a lm@email John Smith jos@email
Amanda Kessel employee a ak@email John Smith jos@email
Derek Jeter employee b dj@email Jane Smith jas@email

I’ve looked through and tried documentation at:

https://www.sqltutorial.org/sql-self-join/

SQL Server : LEFT JOIN EMPLOYEE MANAGER relationship

One of the big differences here is I don’t have any employee or manager id column to work with.

If you’re a supervisor for a division, ie John Smith is a supervisor in division a, then you manage all the employees in division a. Meanwhile, all the supervisors answer to the boss in division o, while the boss answers to no one.

Here is the best code I’ve tried so far:

select e.*, b.employee as supervisor, b.email as supervisor_email
from employees e, employees b
where b.division = e.division
and
b.title like '%supervisor%'

This got me close, it returned:

employee title division email supervisor_name supervisor_email
John Smith supervisor a jos@email John Smith jos@email
Jane Smith supervisor b jas@email Jane Smith jas@email
Leo Messi employee a lm@email John Smith jos@email
Amanda Kessel employee a ak@email John Smith jos@email
Derek Jeter employee b dj@email Jane Smith jas@email

So, it got the employee info right, but left out the Boss record and placed the supervisors as their own supervisor. I think I need some kind of case or if statement here, but I’m not sure.

Please let me know if this makes sense or if any further clarification is needed.

>Solution :

You could try using a LEFT JOIN and work with two conditions:

  • when division is the same and we’re dealing with the relationship employee < supervisor
  • when the relationship is supervisor < boss

Here’s how I did it:

SELECT t1.*,
       t2.employee,
       t2.email
FROM      tab t1
LEFT JOIN tab t2
       ON (t1.division = t2.division AND 
           t2.title = 'supervisor' AND 
           t1.title = 'employee') 
       OR (t2.title = 'boss' AND 
           t1.title = 'supervisor')

You’ll find an SQL fiddle here.

Leave a Reply