0

I am running the following query

select * from Policy
where policynumber in
(
select policynumber from [Email]
where newsletteroptin = 'Y'
and
Source NOT IN ('CWS')
)

I also have tried removing source and I am still getting records those newsleter options are N.

The results which I am getting it's pulling everything from email table even records those newsletteroptin option is N and even sources those do contain CWS...

I want to pull all records from Policy table those have newsletter optin to Y but source is not equal to CWS.

  • 1
    Please note SQL != SOQL. – Adrian Larson Oct 13 '17 at 21:33
  • Is there a 1:1 relationship between policy and email rows? – Adam Spriggs Oct 14 '17 at 13:44
  • No. It's one(policy) to many(email). – Sohail Oct 16 '17 at 13:14
  • What's the primary key on the target data extension? – Adam Spriggs Oct 16 '17 at 17:32
  • Can you give us some sample rows from both data extensions and your desired output? – Adam Spriggs Oct 16 '17 at 17:33
4

First, you'll get into trouble in using wildcards instead of listing out the columns in your SFMC queries. The columns are cached.

Second, I'd suggest selecting the newsletteroptin and source for verification -- assuming that there's a 1:1 relationship between policy and email.

select 
  p.policynumber
, e.newsletteroptin
, e.source
from Policy p
inner join Email e on p.policynumber = e.policynumber
where e.newsletteroptin = 'Y'
and e.source != 'CWS'
  • It's not one to one relation. It's one to many (policy to email). – Sohail Oct 16 '17 at 13:14
  • ok, problem solved. I added all matching primary keys from both tables into inner join clause and problem solved. so it becomes like this: INNER join [Email] e on C.policynumber = e.policynumber AND C.Email = E.Email AND C.OriginalDOB = E.OriginalDOB Where.... Thank you all, for helping me to resolve this issue. – Sohail Oct 18 '17 at 16:59
  • I still think it'd be helpful if you updated your question with the answers to my two questions in the comments above. – Adam Spriggs Oct 19 '17 at 2:05

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.