• 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/