SQL Server subquery for manager-employee relationship-CodePudding

Given the sample data below. How could I get a list of employee ids by project excluding the employees' managers? I've tried many versions of sub-queries and can remove all managers (employees with no direct reports) but that's not right.

emp_id  project_num 
-----   --------
e1      1
e2      1
e3      1
e5      4
e6      4
e7      4

Employee table:

emp_id  mgr_id
------  ------
e1      e3
e2      e9
e3      e8 (mgr of e1 also on proj 1)
e5      e4
e6      e0 (mgr of e7 also on proj 4)
e7      e6

Result example:

1 | e1
1 | e2
4 | e5
4 | e7

Project 1 excludes e3 and project 4 excludes e6 because they are managers of employees on those same projects.

CodePudding user response：

create table projects
(
    emp_id char(3),
    project_num int
)
create table emp
(
    emp_id char(3),
    mgr_id char(3)
)

insert into projects
values
    ('e1', 1),
    ('e2', 1),
    ('e3', 1),
    ('e5', 4),
    ('e6', 4),
    ('e7', 4),
    ('e10', 5),
    ('e3', 5)

insert into emp
values
    ('e1', 'e3'),
    ('e2', 'e9'),
    ('e3', 'e8'),
    ('e5', 'e4'),
    ('e6', 'e0'),
    ('e7', 'e6'),
    ('e10', 'e11')  
  

select project_num, emp_id
from projects p
where p.emp_id not in (
 select mgr_id
from projects p1
    join emp e on e.emp_id = p1.emp_id
where p1.project_num = p.project_num
)

/*
Output
1   e1 
1   e2     
4   e5 
4   e7 
5   e10
5   e3 
*/

I added a couple of more entries so ..

e3 excluded in project 1 since its manager to e1
e3 would be displayed in project 5 since its manager to no one in project 5