Joining a table to itself

Joining Tables or Database Records › Joining Tables › Joining a table to itself

Joining a table to itself

Why join a table to itself?

You join a table to itself when one column in a table requires the table itself to supply additional information. For example, table EMP has a column of manager IDs. To find the name of Michael Angelo's manager, you find the manager's ID in the MGRID column and then find the same ID in the EMPID column. The manager's name is associated with the employee ID:

EMP table

┌──────────────────────────────────────┐
│ EMPID   EMPNAME               MGRID  │
│                                      │
│ 0075    Jennifer Lanzarotta   0003   │
│ 3302    Bart Elopoulos        0004   │
│ 3871    Reginald Mahoney      0007   │
│ 4230    Duc Ho                0011   │
│ 6264    Daniel Ortega         0013   │
│ 6348    Edith Jones           0015   │
│ 7170    Anthea Poznanski      0075   │
│ 8939    Ankur Sahu            0321   │
│ 8957    William Sternbach     0349   │
└─────────────────┬────────────────────┘
                  │
                  │
                  │
                  │
                  │  Join table EMP to itself by equating Anthea
                  │  Poznanski's manager ID to an employee ID
                  │
                  │
┌─────────────────▼────────────────────────────────────────────────────────────┐
│ EMPID   EMPNAME               MGRID     EMPID   EMPNAME               MGRID  │
│                                                                              │
│ 7170    Anthea Poznanski      0075      0075    Jennifer Lanzarotta   0003   │
└──────────────────────────────────────────────────────────────────────────────┘

This type of join is called a reflexive join and is used to implement a nested structure, which is also called a bill-of-materials structure. A nested structure is one where there is a relationship between columns in the same table. For example, nested relationships exist:

In an industrial environment where a part in a component of another part and can contain component parts itself. For example, a door is a component of a car and contains these component parts: handle, lock, and window.
In the corporate environment used for examples in this manual where an employee manages other employees. Likewise, an employee can report to more than one supervisor.

How to join a table to itself

To join a table to itself, you simply treat the table as two tables by assigning aliases, or alternative names, to the table in the SELECT statement. Thereafter, the SELECT statement coding requirements listed below are the same as if you were joining two different tables. This figure illustrates how to join table EMP to itself by assigning two alias table names— MANAGER and WORKER:

                     EMP table
                     ┌──────────────────────────────────────┐
                     │ EMPID   EMPNAME               MGRID  │
                     │                                      │
                     │ 0075    Jennifer Lanzarotta   0003   │
                     │ 3302    Bart Elopoulos        0004   │
                     │ 3871    Reginald Mahoney      0007   │
                     │ 4242    Chinua Achebe         0075   │
                     │ 7170    Anthea Poznanski      0075   │
                     └──────────────────┬───────────────────┘
                                        │
                                        │ Assign alias table names
                                        │
                   ┌────────────────────┴────────────────┐
                   │                                     │
                   │                                     │
MANAGER table      │                                     │
┌──────────────────▼───────────────────┐                 │
│ EMPID   EMPNAME               MGRID  │                 │
│                                      │                 │
│ 0075    Jennifer Lanzarotta   0003   │                 │
│ 3302    Bart Elopoulos        0004   │                 │
│ 3871    Reginald Mahoney      0007   │                 │
│ 4242    Chinua Achebe         0075   │                 │
│ 7170    Anthea Poznanski      0075   │                 │
└──────────────────┬───────────────────┘                 │
                   │                                     │
                   │                     WORKER table    │
                                         ┌───────────────▼──────────────────────┐
    Join the MANAGER and WORKER tables   │ EMPID   EMPNAME               MGRID  │
    by equating the ID of the worker's   │                                      │
    manager to the employee ID of the    │ 0075    Jennifer Lanzarotta   0003   │
    manager                              │ 3302    Bart Elopoulos        0004   │
                   │                     │ 3871    Reginald Mahoney      0007   │
                   │                     │ 4242    Chinua Achebe         0075   │
                   │                     │ 7170    Anthea Poznanski      0075   │
                   │                     └───────────────┬──────────────────────┘
                   │                                     │
                   └───────────────────┬─────────────────┘
                                       │
                                       │
┌──────────────────────────────────────▼─────────────────────────────────────┐
│ MANAGER                                WORKER                              │
│                                                                            │
│ EMPID   EMPNAME               MGRID    EMPID   EMPNAME               MGRID │
│                                                                            │
│ 0075    Jennifer Lanzarotta   0003     4242    Chinua Achebe         0075  │
│ 0075    Jennifer Lanzarotta   0003     7170    Anthea Poznanski      0075  │
└────────────────────────────────────────────────────────────────────────────┘

Coding the SELECT statement

To join a table to itself, follow these steps:

Qualify each column listed after the SELECT keyword with an alias table name: manager.lastname
For each reflexive join (that is, for each time you join a table to itself), assign an alias by coding:
1. The table name
2. A blank
3. The alias
Separate each table and its alias from another with a comma: emp manager, emp worker
In the WHERE clause, compare two columns that share the same type of information: manager.empid = worker.mgrid

Example

List each manager and associated staff. To retrieve this information, join the EMP table to itself, equating a manager's employee ID to the ID of a staff member's manager.

The SELECT statement assigns these aliases to EMP table: MANAGER and WORKER. The WHERE clause selects rows where the employee ID in the MANAGER table equals the manager's ID in the WORKER table. The columns display the manager's name retrieved from the MANAGER table and the worker's name retrieved from the WORKER table:

select manager.lastname as supervisor, worker.lastname as staff
  from emp manager, emp worker
  where manager.empid = worker.mgrid ! display

EMP/EMP REPORT mm/dd/yy SUPERVISOR STAFF --------------- --------------- GARFIELD JENSEN GARFIELD JACOBI GARFIELD TYRO GARFIELD DOUGH GARFIELD HEAROWITZ GARFIELD GRANGER GARFIELD GALLWAY GARFIELD LITERATA BANK ANGELO BANK MCDOUGALL BANK PENMAN BANK JACKSON BANK ZEDI HENDON RUPEE HENDON GARFIELD HENDON BANK - 1 -