Business case 5: Outer joins between attribute lookup tables

A common request is the ability to generate an outer join between attribute lookup tables for a report that contains only attributes (that is, no metrics). For example, consider the tables below.

EMPLOYEE		EMERGENCY CONTACT		DEPARTMENT
EMP_ID		EMP_ID		DEPT_ID
FIRST_NAME		CONTACT_FIRST_NAME		DEPT_NAME
LAST_NAME		CONTACT_LAST_NAME		BUS_UNIT_ID
HIRE_DATE		CONTACT_PHONE_NUMBER
DEPT_ID

Given this structure, you could model an attribute hierarchy as follows:

•

Business Unit -< Department -< Employee

•

Hire Date -< Employee

•

Emergency Contact -< Employee

In addition, the relationship between Employees and Emergency Contacts is such that each employee may have up to one contact, which means not all employees have contacts on record. One of the reports you probably would like to create may look like the following:

Employee	Department	Emergency Contact	Phone Number
Gonzalez, James	Marketing
Dawson, John	Finance	Dawson, Jane	555-1212
Larkins, Abraham	R & D	Taylor, Mary	555-3456
Walker, George	Finance	Walker, Martha	555-9876
...	...	...	...

NULLS are displayed for employees who do not have emergency contacts.

However, if you model the attributes as described below, you would not get the desired output:

•

Employee:

▫

@ID = EMP_ID, @[First Name] = FIRST_NAME, @[Last Name] = LAST_NAME

▫

Tables: EMPLOYEE (lookup), EMERGENCY_CONTACT

•

Department:

▫

@ID = DEPT_ID

▫

Tables: DEPARTMENT (lookup), EMPLOYEE

▫

Child: Employee

•

Hire Date:

▫

@ID = HIRE_DATE

▫

Tables: EMPLOYEE (lookup)

▫

Child: Employee

•

Emergency Contact:

▫

@ID = CONTACT_PHONE_NUMBER, @[First Name] = CONTACT_FIRST_NAME, @[Last Name] = CONTACT_LAST_NAME

▫

Tables: EMERGENCY_CONTACT (lookup)

▫

Child: Employee

Using the above model, the SQL generated would join the EMPLOYEE table to the EMERGENCY_CONTACT table, and only those employees who have emergency contacts would appear in the final result. In order to see all employees, you can perform an outer join using a logical view, described as follows.

Using a logical view for an outer join

To perform an outer join for the case described above, you can use the following SQL and the list of columns to map to the view:

select E.EMP_ID,
E.FIRST_NAME,
E.LAST_NAME,
E.HIRE_DATE,
E.DEPT_ID,
C.CONTACT_FIRST_NAME,
C.CONTACT_LAST_NAME,
C.CONTACT_PHONE_NUMBER
from EMPLOYEE E
left outer join EMERGENCY_CONTACT C
on (E.EMP_ID = C.EMP_ID)

LVW_EMERGENCY_CONTACT
EMP_ID
FIRST_NAME
LAST_NAME
HIRE_DATE
DEPT_ID
CONTACT_FIRST_NAME
CONTACT_LAST_NAME
CONTACT_PHONE_NUMBER

Make sure to include all columns from the original child table (for example, EMPLOYEE). The new logical table LVW_EMERGENCY_CONTACT can then be used to define attributes as follows:

•

Employee:

▫

@ID = EMP_ID, @[First Name] = FIRST_NAME, @[Last Name] = LAST_NAME

▫

Tables: EMPLOYEE (lookup), LVW_EMERGENCY_CONTACT

•

Department:

▫

@ID = DEPT_ID

▫

Tables: DEPARTMENT (lookup), EMPLOYEE, LVW_EMERGENCY_CONTACT

▫

Child: Employee

•

Hire Date:

▫

@ID = HIRE_DATE

▫

Tables: EMPLOYEE (lookup), LVW_EMERGENCY_CONTACT

▫

Child: Employee

•

Emergency Contact:

▫

@ID = CONTACT_PHONE_NUMBER, @[First Name] = CONTACT_FIRST_NAME, @[Last Name] = CONTACT_LAST_NAME

▫

Tables: EMERGENCY_CONTACT (lookup), LVW_EMERGENCY_CONTACT

▫

Child: Employee

The Employee attribute is not represented in the original EMERGENCY_CONTACT table and all attributes represented in the EMPLOYEE table are also represented in the LVW_EMERGENCY_CONTACT table.

Now if we run a report with Employee and Emergency Contact attributes, the EMPLOYEE table will be outer joined to the EMERGENCY_CONTACT table, and NULLs will be returned for any employees who do not have emergency contacts. Also note that if we run a report that includes only the Employee attribute, it will be executed against the EMPLOYEE table; the EMERGENCY_CONTACT table will be joined only when necessary.

This technique is applicable any time that the lookup tables should always be outer joined. The technique does not work when the lookup tables should sometimes be outer joined and sometimes be inner joined.