Logical Tables » Logical view examples » Business case 5: Outer joins between attribute lookup tables

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.