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>

Related examples in the same category

1.	WHERE clause in the subquery's SELECT statement links the inner query to the outer query
2.	Correlated subquery using the department table in both inner and outer queries
3.	Correlated subquery: the inner query depends on the outer query for any of its values
4.	Correlated subquery using Distinct
5.	Leaf-Level Employees (Employees with No Subordinates), Correlated Subquery Syntax

Leaf-Level Employees (Employees with No Subordinates), Join Syntax : Correlated subquery « Subquery « SQL Server / T-SQL

Related examples in the same category