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.

  • 1
    Can you update your question with a brief set of sample data and the desired output? Commented May 23, 2019 at 11:39

1 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:


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

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

  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:

FROM MasterContacts


FROM MasterContacts



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

Let me know if that helps!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .