Leaf-Level Employees (Employees with No Subordinates), Join Syntax
3>
4>
5>
6> CREATE TABLE Employees
7> (
8> empid int NOT NULL,
9> mgrid int NULL,
10> empname varchar(25) NOT NULL,
11> salary money NOT NULL)
12> 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> M.*
19> FROM
20> Employees AS M
21> LEFT OUTER JOIN
22> Employees AS E ON M.empid = E.mgrid
23> WHERE
24> E.mgrid IS NULL
25> 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
1>