Tao Klerks (11/18/2008)
Hi slange,I think you're confusing a couple of possibilities.
The query above reports employees who have no "subordinates" - there are no employees that have their employeeid in the reportsto field.
The query that you are thinking of is almost exactly the opposite:
select *
from employees
left join employees as supervisors on employees.reportsto = supervisors.employeeid
where supervisors.employeeid Is Null
and employees.reportsto Is Not Null
It's actually slightly different because it also has an extra WHERE criterion - making sure that the employees returned actually have a reportsto value (for a supervisor that no longer exists, or never existed).
Does this help?
Try this and you will see what I mean. 🙂
create table #employees (employeeid int, reportsto int)
insert #employees select 10, null
insert #employees select 45, 10
--here is the original query
select *
from #employees
left join #employees as subordinates on #employees.employeeid = subordinates.reportsto
where subordinates.employeeid Is Null
--now delete the reportsto record
delete #employees where employeeid = 10
--they still show up
select *
from #employees
left join #employees as subordinates on #employees.employeeid = subordinates.reportsto
where subordinates.employeeid Is Null
drop table #employees
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/