Sample SQL with self-join - using SQL Server 2005.

Assume an employee table that looks like this:

empiddeptsalarymgrid
100SALES95000200
200SALES100000990
300SALES105000200
400ACCTG110000990
500ACCTG115000400
600ACCTG120000400
700ADMIN125000900
800ADMIN130000900
900ADMIN127000990
990DAMAN98000 


Query 1: find how many employees in each department are paid more than their managers.
Only count non-managerial employees.

select E.dept, count(M.mgrid) As NumOverPaidEmps
  from employees E, employees M
  where E.salary > M.salary
      and E.mgrid = M.empid
      and M.mgrid IS NOT NULL
  group by E.dept;

Result:

deptNumOverPaidEmps
ACCTG2
ADMIN1
SALES1
 


Query 2: find how many employees in each department are paid more than their managers.
Same as above, but include managers this time.

select E.dept, count(*) As NumOverPaidEmps
  from employees E, employees M
  where E.salary > M.salary
      and E.mgrid = M.empid
  group by E.dept;

Result:

deptNumOverPaidEmps
ACCTG3
ADMIN2
SALES2



Query 3: Now who are these non-managerial employees we counted in query 1?
They are:

select E.dept, E.empid
  from employees E, employees M
  where E.salary > M.salary
      and E.mgrid = M.empid
      and M.mgrid IS NOT NULL;

Result:

deptempid
SALES300
ACCTG500
ACCTG600
ADMIN800
 


Query 4: Finally, who are these employees and managers we counted in query 2 above?
They are:

select E.dept, E.empid
  from employees E, employees M
  where E.salary > M.salary
      and E.mgrid = M.empid;

Result:

deptempid
SALES200
SALES300
ACCTG400
ACCTG500
ACCTG600
ADMIN800
ADMIN900



Valid XHTML 1.0 Transitional Valid CSS!
 

MCP icon
MCTS icon