Forum Discussion

matt_bostrom's avatar
matt_bostrom
New Contributor II
7 years ago

How to join records with duplicates

i have unique emails on the left side of the join for emails that have previously bounced from a mailing list. i have contacts from our CRM on the right side of the join and in some cases we have duplicates of the same email (they are valid duplicates b/c they are tied to specific user records that span multiple entities in our platform so no chance of cleanup). I can’t seem to get the join to work when there are duplicate values.

Example: Left side of the snap has an email jdoe@acme.com. The right side from our CRM yields 3 contact records for that email jdoe@acme.com:

  1. Id = 123, email = jdoe@acme.com
  2. Id = 456, email = jdoe@acme.com
  3. Id = 789, email = jdoe@acme.com

I’d like to map/mark each instance of the records above as “undeliverable = true”… but when I try a join the duplicates are left out. i’ve tried inner and outer joins. any guidance on this is appreciated.

1 Reply

  • del's avatar
    del
    Contributor III
    7 years ago

    Matt, It seems the inner join should work. The fact that you’re generating even 1 record in the output indicates to me that the join may be configured correctly. So, my first grasp at the air is maybe case sensitivity. If the CRM application has different casings for jdoe for each of the different contact records, then the join on email addresses won’t match those that are different than the left side. You might try standardizing with a $email.toLowerCase() before the join. Hope that helps.