Alternate Contact Matching

[mhavey]

I want to use Valentine to check if two tables are joinable. The first table is Encounter: (cust_nbr, phone, email). The second table is CustomerContacts (cust_nbr, phone, email).

The relationship between these tables is messy:

• An encounter might not have a cust_nbr value. But its phone and email values might map to values in CustomerContacs table.

Example:
encounter (null, 1112223333, [email protected]) matches (cust-123, 1112223333, null)
encounter (null, 1112223333, [email protected]) matches (cust-123, null, [email protected])
encounter (null, 1112223333, [email protected]) probably matches (cust-123, 2223334444, [email protected])

• If an encounter has a cust_nbr value, there will be a record in CustomerContact with that cust_nbr, but the encounter's phone and email might not match the values in the CustomerContact table

encounter (cust-234, 2112223333, [email protected]) matches (cust-234, 9999999999, [email protected])

• A customer can have several contacts, and zero or more can match an encounter:

encounter (cust-345, 3112223333, [email protected]) matches both (cust-345, 9999999999, [email protected]) and (cust-345, 3112223333, [email protected])

• Some encounters do not match any CustomerContact records.

My desired result is that if MOST ENCOUNTERS match to SOME customer contact, the tables are joinable. Looking for advice how to test this match.