1

I have a SQL query that puts records from one DE to another DE if that record does not exists in the second DE. The base works fine for me and i have the following query:

select t.Emailaddress 
from [All_subscribers_totaal] t
where t.Emailaddress not in ( 
select p.Emailaddress
from [Pak_t_Nieuws] p
) 

But when i try to add another DE (so i want to check if a record does not exists in DE A AND B AND C etc)it does not work even i don't get an error like so:

select t.Emailaddress 
from [All_subscribers_totaal] t
where t.Emailaddress not in ( 
select p.Emailaddress
from [Pak_t_Nieuws] p
) 
OR t.Emailaddress not in ( 
select pm.Emailaddress
from [Pak_t_Nieuws_Medewerkers] pm
)

Is there something i missed?

Thanks

0

I quickly created this scenario and tested as I need to build something like this.

enter image description here

SELECT A.ID
FROM 
DE_A A
FULL OUTER JOIN  
(
SELECT ID FROM DE_B

UNION

SELECT ID FROM DE_C

) B

ON A.ID = B.ID

WHERE
A.ID is null OR  B.ID IS NULL

Your SQL should be as follow..

select t1.Emailaddress 
from [All_subscribers_totaal] t1
full outer join
(
select Emailaddress
from 
[Pak_t_Nieuws] 

UNION

select Emailaddress
from
Pak_t_Nieuws_Medewerkers

) t2
ON t1.Emailaddress = t2.Emailaddress

WHERE
t2.Emailaddress IS NULL
  • You can join UNION as many Data extension you want and exclude from source – Fayyaz A Mian Feb 7 '19 at 20:06
  • This will return results from any of the DEs. OP just wanted those in original table that did not exist inside of the other DEs. – Gortonington Feb 7 '19 at 22:11
  • Thanks Gortonington, To achieve that he just change the where clause to where t2.Emailaddress IS NULL – Fayyaz A Mian Feb 7 '19 at 22:15
  • 1
    correct - I wanted to comment so you can adjust your answer to fit the posters request. – Gortonington Feb 7 '19 at 22:17
  • Thank you so much for the answer. But i got the same issue. I got this error problem: Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'Contact_delete'. The duplicate key value is (Emailaddres form a record). If i make the Emailaddress not primary i got an error that at least i should have 1 primary key and 1 non primary. How can i solve this problem? I can try another field as 2 primary keys but i don't get the query right for it – Martijn e Feb 8 '19 at 8:01
0

What I usually do in this case instead of making multiple WHERE statements is to UNION inside of a single NOT EXISTS statement.

select t.Emailaddress 
from [All_subscribers_totaal] t
where NOT EXISTS ( 
  select p.Emailaddress
  from [Pak_t_Nieuws]

  UNION

  select pm.Emailaddress
  from [Pak_t_Nieuws_Medewerkers] pm
)

Using NOT IN, should have the same effect, but I usually lean towards NOT EXISTS or LEFT JOIN with WHERE 2ndtable.pkey IS NULL

If this still returns empty results, I would verify that

  1. It is not timing out or otherwise erroring
  2. That there are values inside that DE that do not exist in the other DEs
  • Thanks for your answer. Still no results and i see no errors. But what do you mean with values inside the DE that do nog exist in the other DE's? I only ask for the Emailaddress right? – Martijn e Feb 7 '19 at 16:12
  • yes, make sure that there are values that should be pulled into your target DE – Gortonington Feb 7 '19 at 17:00
0

Try the left join approach

This should return any emails that exist in [All_subscribers_totaal] and not in the either of the other 2 DE. Change the OR to a AND if you would like to retrieve records if they don't exist in both DE's

SELECT t.Emailaddress from [All_subscribers_totaal] t left join [Pak_t_Nieuws] p ON p.Emailaddress = t.Emailaddress left join [Pak_t_Nieuws_Medewerkers] pm ON pm.Emailaddress = t.Emailaddress Where p.Emailaddress is null OR pm.Emailaddress is null

  • Hi Noor Alam, Thanks for your answer. I tried your option and got this error problem: Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'Contact_delete'. The duplicate key value is (Emailaddres form a record). If i make the Emailaddress not primary i got an error that at least i should have 1 primary key and 1 non primary. How can i solve this problem? I can try another field as 2 primary keys but i don't get the query right for it. – Martijn e Feb 8 '19 at 7:45
  • Sounds like you have duplicate email address being inserted in to the DE. Try SELECT DISTINCT T.Emailaddress in the SQL and have the PK as Email Address – Noor Alam Feb 8 '19 at 12:38

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.