SQL multiple WHERE clause problem

Question

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

Answer 1

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

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

Answer 2

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

Answer 3

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