Catch-All Join To A Lookup Dimension

I recently ran into an interesting problem that I’d like to share and show how I resolved it. The solution involves a catch-all join to a lookup dimension table.

ERD Diagram of wildcard lookup status table.
ERD Diagram of wildcard lookup status table.

Imagine having many employees that work in many departments. Each department has their own way of determining the employee’s status; Some departments use the status code that was given in the source system, other departments rely solely on the department they’re from and others use a combination of both! Oh yeah, the fun bit, this status logic can change…

How to solve with a catch-all join to a lookup table?

The first step to solving this problem is to create a lookup dimension that will be used by the presentation layer and possibly an ETL process to retrieve the proper status. This table will store the key(s) and value(s) pairs required to locate a particular status. A zero-to-many to zero-to-many relationship between the existing employee table and the new lookup table will exist.

Let’s insert some test data to play with:

Notice that I’m using an asterisk (*) to denote a wildcard, catch-all value, meaning anything can match. So, in this example, Max will match to an Inactive status because his status code is an ‘A’, and the rule states that no matter which department an employee is employed if the employee has a status of ‘A’, they are considered inactive.

How would a join work between these two tables?

Let’s take a look!

With the use of a case statement in the join condition, we can make use of the catch-all join functionality of the lookup table. Pretty cool, right?

To see this in action, take a look at this example in a SQL Fiddle!