I know enough to know that I should be embarrassed to ask this question...

In SFMC I have two basic data extensions - both the result of imports from our CRM.

Data Extension 1 (DE 1) has two fields called "Contact: email" and "Record Type" Data Extension 2 (DE 2) has a single field called "email"

The destination Data Extension has two fields: "Contact: email" and "Record Type"

What I want the query to do is compare DE 1 against DE 2 and EXCLUDE any contact from DE 1 that is also on DE 2. Kind of an ad hoc suppression list.

Here is the query:

 Select [Contact: Email],
 [Record Type]
 From DE 1
 Where [Contact: Email] NOT IN
 (select Email from DE 2)

What am I doing wrong?

  • 1
    what type of error are you getting? Is it providing results, just not what you would expect? – Gortonington Feb 5 '19 at 14:10
  • Hi @UTScreenwright, don't worry about the question you're asking. We all started at some point. Your question is very well formulated and we can see that you have tried to find the solution on your own. – Martin Lezer Feb 5 '19 at 14:10
  • @Gortonington - no errors...just no results. I did a test of this query and expected to get 2 contacts on my destination Data Extension. The query ran successfully...just no results. – UTScreenwright Feb 5 '19 at 14:21
  • 1
    @MartinLezer - thank you... it drives me nuts though that I'm burning goodwill on [what I think is] a very 101 question. I want my money back on my Udemy class! – UTScreenwright Feb 5 '19 at 14:23
  • (just kidding Udemy) – UTScreenwright Feb 5 '19 at 14:23

You might want to try the EXISTS operator:

    [Contact: Email],
    [Record Type]
    [DE 1] a
    not exists
            [DE 2] b
            b.Email = a.[Contact: Email]
  • This did the trick. Thank you very much @Rufenstein. – UTScreenwright Feb 5 '19 at 16:41

Possibilities : -there is a space in your data extension name so you need to add brackets too : [DE 1] (if it's not a placeholder name) -your Email fields are text type and don't have the same length

  • I can rule out the spaces in the data extensions name (they are all camel case). But I did have inconsistent data types across DE 1, DE 2 and the destination DE. I'm going to fix that now... – UTScreenwright Feb 5 '19 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.