Search code examples
sqlsql-server

Finding employees who are not managers

I have a table where we have staff_id and manager_id. Attached screenshot.

I found managers by using this query:

select e.first_name as employee , m.first_name as manager
from sales.staffs E
inner JOIN sales.staffs M ON M.staff_id = E.manager_id

How do I pull a list of employees who are not managers?

My sample table

My sample table

Solution

  • use not exists

    select t1.* from from sales.staff t1
         where not exists ( select 1 from sales.staff t2 where t1.staff_id=t2.manager_id )

    if you use not in then do null chechk

    select t.* from sales.staff  t 
where t.staff_id not in (select manager_id from sales.staff where manager_id IS NOT NULL)