Dynamically change node manager to null

Question

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

In our database, each department head also reports to the general director or chairman. How can I change the reportsToEmpId column (at run time) for the department head to NULL? If the parent of the department head is not part of this department.

Here are my reports for the table structure:

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

Since I pull only deptId = 1 , and Jim not dept1 . Can I dynamically change the John 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 the tmp table, but this seems too cumbersome.

Original conclusion:

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

Here is my expected result: (However, I prefer not to make any changes to the original table, because otherwise, if I want to attract everyone who reports to the CEO, I lost the “connection” between the departments).

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

Answer 1

You can leave the connection on both empId and deptId . If the reported employee does not 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 must use 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