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.

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

1 Answer 1

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'
3
  • It's not one to one relation. It's one to many (policy to email).
    – Sohail
    Oct 16, 2017 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, 2017 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. Oct 19, 2017 at 2:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .