A Guide to SQL (Available Titles Skills Assessment Manager (SAM) by Office 2010)

Author:Office 2010)
Language: eng
Format: epub

the number of the employee’s manager, who also is an employee. If you look at the row for

employee 206 (Joan Dykstra), you will see that employee 198 (Mona Canzler) is Joan’s

manager. By looking at the row for employee 198 (Mona Canzler), you see that her man-

ager is employee 108 (Martin Holden). In the row for employee 108 (Martin Holden), the

manager number is null, indicating that he has no manager.

149

Employee 108

has no manager

Employee 198

manages employee

206

FIGURE 5-14

Employee and manager data

Suppose you need to list the employee number, employee last name, and employee first

name along with the number, last name, and first name of each employee’s manager. Just

as in the previous self-join, you would list the EMPLOYEE table twice in the FROM clause

with aliases.

The command shown in Figure 5-15 uses the letter E as an alias for the employee and

the letter M as an alias for the manager. Thus E.EMPLOYEE_NUM is the employee’s num-

ber and M.EMPLOYEE_NUM is the number of the employee’s manager. In the SQL com-

mand, M.EMPLOYEE_NUM is renamed as MGR_NUM, M.LAST_NAME is renamed as

MGR_LAST, and M.FIRST_NAME is renamed as MGR_FIRST. The condition in the

WHERE clause ensures that E.MGR_EMPLOYEE_NUM (the number of the employee’s man-

ager) matches M.EMPLOYEE_NUM (the employee number on the manager’s row in the

table). Employee 108 is not included in the results because Martin Holden has no man-

ager (see Figure 5-14).

Multiple-Table Queries

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.