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, a@b.com) matches (cust-123, 1112223333, null)
encounter (null, 1112223333, a@b.com) matches (cust-123, null, a@b.com)
encounter (null, 1112223333, a@b.com) probably matches (cust-123, 2223334444, a@b.com)

• 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, b@b.com) matches (cust-234, 9999999999, something@something.com)

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

encounter (cust-345, 3112223333, c@c.com) matches both (cust-345, 9999999999, c@c.com) and (cust-345, 3112223333, xyz@personal.com)

• 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.