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

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.

CodePudding user response：

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 boss < supervisor

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.