Dynamically change supervisor node to null

Question

I am pulling employee data from a SQL Server database to draw an org chart. My company has many departments (I think every company does). I only wanted to pull one specific department at a time.

On our database, every department head also reports to CEO or chair.. How can I change the reportsToEmpId column (at run time) for a department head to NULL? If the parent of the department head is not belong to this department.

Here is my reportsTo table structure:

empId, name, reportsToEmpId, deptId
100, John, 99, 1
101, Mary, 100, 1
102, Carol, 100, 1
99, Jim, null, 2

Since I only pull deptId = 1, and Jim is not dept1. Can I dynamically change John's reportsToEmpId column to NULL?

select fields I need
from reportsTo r
join employee e on r.empId = e.empId
join groupHightlight h on ...
where deptId=1

I tried to use tmp table but it seems too cumbersome.

Original output:

empId, name, reportsToEmpId, deptId
    100, John, 99, 1
    101, Mary, 100, 1
    102, Carol, 100, 1

Here is my expected output: (However, I prefer not to make any change on the original table, since otherwise if I want to pull everyone who reports to CEO, then I lost the "connection" between departments).

empId, name, reportsToEmpId, deptId
    100, John, NULL, 1
    101, Mary, 100, 1
    102, Carol, 100, 1

Answer 1

You can left join on both empId and deptId. If the employee being reported to doesn't have the same deptId, then reportsToEmpId will be null:

select r.empId, r.name, r2.empId reportsToEmpId, r.deptId
from reportsTo r
left join reportsTo r2
    on r2.empId = r.reportsToEmpId and r.deptId = r2.deptId
where r.deptId=1

Answer 2

You have to use a Self Inner Join to achieve this

UPDATE T1
SET T1.reportsToEmpId = NULL
FROM reportsTo T1 INNER JOIN reportsTo  T2
     ON T1.reportsToEmpid = T2.empId
WHERE   T1.deptid <> T2.deptId AND T1.deptID = 1