Mega Code Archive
Leaf-Level Employees (Employees with No Subordinates), Correlated Subquery Syntax
<![CDATA[
5>
6>
7>
8> CREATE TABLE Employees
9> (
10> empid int NOT NULL,
11> mgrid int NULL,
12> empname varchar(25) NOT NULL,
13> salary money NOT NULL)
14> GO
1> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(1, NULL, 'Nancy', $10000.00)
2> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(2, 1, 'Andrew', $5000.00)
3> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(3, 1, 'Janet', $5000.00)
4> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(4, 1, 'Margaret', $5000.00)
5> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(5, 2, 'Steven', $2500.00)
6> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(6, 2, 'Michael', $2500.00)
7> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(7, 3, 'Robert', $2500.00)
8> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(8, 3, 'Laura', $2500.00)
9> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(9, 3, 'Ann', $2500.00)
10> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(10, 4, 'Ina', $2500.00)
11> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(11, 7, 'David', $2000.00)
12> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(12, 7, 'Ron', $2000.00)
13> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(13, 7, 'Dan', $2000.00)
14> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(14, 11, 'James', $1500.00)
15>
16>
17> SELECT *
18> FROM
19> Employees AS M
20> WHERE
21> NOT EXISTS
22> (
23> SELECT
24> empid
25> FROM
26> Employees AS E
27> WHERE
28> E.mgrid = M.empid
29> )
30> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
empid mgrid empname salary
----------- ----------- ------------------------- ---------------------
5 2 Steven 2500.0000
6 2 Michael 2500.0000
8 3 Laura 2500.0000
9 3 Ann 2500.0000
10 4 Ina 2500.0000
12 7 Ron 2000.0000
13 7 Dan 2000.0000
14 11 James 1500.0000
(8 rows affected)
1>
2>
3> drop table Employees;
4> GO
]]>