Contacts with multiple email addresses tied to single ID - SQL Join

Question

We have 2 data extensions which we would like to join.

The first called "MasterContacts" has contact information, including multiple email addresses tied to one FederationID, which is present in the DE (notable, this DE extension, the Primary Key is the column called Contact_Key).

The second data extension called "Permissions" has FederationID as Primary Key together with information about different channel preferences and global opt-in.

Now, as the end-result we would like to perform a JOIN ensuring that in the resulting data extensions Contacts can have multiple email addresses stored, meaning a single FederationID can be repeated multiple times if a contact has multiple email addresses stored for him.

Answer 1

depending on how I interpret your question (and the data extensions look like), I see two scenarios: 1) you use an INNER JOIN or 2) you first break up the first DE and then use a UNION.

However if you struggle with these kind of JOINS you may want to look a a drag-and-drop solution like DESelect. We have been using it to avoid having to write SQL and it could be a quicker alternative to both solutions below.

SCENARIO 1: All email addresses stored in one column

In this case I assume your first DE looks like:

DE1 - MasterContacts

Contact_Key (Primary Key) | Email | FederationID
001 | [email protected] | 001
002 | [email protected] | 001

I.e. every email address is a different line but FederationID can be repeated.

The second table would look like:

Permissions

FedrationID (PK) | Channel Preference | Opt-In
001 | Email | True
002 | SMS | False

Then your query would achieve the result simply through an INNER JOIN:

SELECT * FROM
MasterContacts
  INNER JOIN Permissions FederationID on FederationID

SCENARIO 2: Email addresses stored in different columns

In this scenario your first table would look different:

DE1 - MasterContacts

Contact_Key (Primary Key) | Email1 | Email2 | Email3 | FederationID
001 | [email protected] | [email protected] | <blank> | 001

In this scenario you will want to do a UNION that sort of first breaks the Email addresses apart and then adds them up in new rows. In other words it will rearrange the columns into rows:

SELECT
  MasterContacts.Email1
  MasterContacts.FederationID
FROM MasterContacts

UNION

SELECT
  MasterContacts.Email2
  MasterContacts.FederationID
FROM MasterContacts

UNION

...

The result of which you can INNER JOIN with the DE2 - Permissions.

Let me know if that helps!