0

I'm trying to create a Data Extension with Subscribers who have received a specific email. All I can find online relates to the JobID, but this email is sent daily and I need it to pull by EmailID.

When I check syntax I keep getting errors. Please help. This is the query.

SELECT su.EmailAddress as Emailaddress,su.SubscriberKey 
FROM _Subscribers su WITH (NOLOCK)
JOIN _Sent se WITH (NOLOCK) 
ON su.SubscriberID = se.SubscriberID
where s.EmailID = 2521896

I've also tried this:

SELECT su.EmailAddress as Emailaddress,su.SubscriberKey 
FROM _Sent se WITH (NOLOCK)
LEFT JOIN _Bounce b WITH (NOLOCK)
ON se.EmailID = b.EmailID
AND se.ListID = b.ListID
AND se.BatchID = b.BatchID
AND se.SubscriberID = b.SubscriberID
JOIN _Subscribers se WITH (NOLOCK) 
ON su.SubscriberID = se.SubscriberID
WHERE s.EmailID = 2521896
AND b.EmailID IS NULL
  • Trying getting distinct subscribers from the subscriber table. Even though you are selecting an email id, I suspect that it is failing before it gets there. Trying using ROW()(OVER ) function in a subquery, or SELCT DISTINCT – Samuel Dare May 19 '17 at 20:55
2

Two things have popped up for me when testing this out:

  1. You are referencing the EmailID as 's.EmailID' when your alias for the _Sent Data View is 'se'

  2. The attribute 'EmailID' is only stored at the 'Job' level. You will need to make another join onto the Job Data View through the JobID field to gain access to that level of information.


SELECT DISTINCT su.EmailAddress, su.SubscriberKey
FROM _Job as j WITH (NOLOCK)
INNER JOIN _Sent as se WITH (NOLOCK) on j.JobID = se.JobID
INNER JOIN _Subscribers as su WITH (NOLOCK) on se.SubscriberID = su.SubscriberID
WHERE j.EmailID = 2521896
  • But the JOB ID is constantly changing. This is for a welcome email sent daily. So would this still fix it? – Emily May 22 '17 at 13:53
  • I just put it in and it seemed to work. I set it to run but it did not bring anything over to my data extension. – Emily May 22 '17 at 14:27
  • Developer support is no help. Please help me to figure out why this query isn't working. Premier support said it was something to do with Select but would not tell me what I needed to do to fix it. – Emily May 22 '17 at 19:32
  • Did you run the query within an automation in Automation Studio? Is the automation showing any errors? If the automation is running fine, you either have an issue with the Data Extension itself or the ID of the email you are entering into the query. I am able to run that query fine on my end and populate a Data Extension (credit to Jim Nagorka for adding that to my post) – AnonWonderer May 23 '17 at 2:18
  • Its still not working with the automation either. No errors, just nothing populates into the data extension. – Emily May 23 '17 at 18:41
0

Finally Support was able to provide me with working code. Seems it was missing ENT

    SELECT DISTINCT su.EmailAddress, su.SubscriberKey 
FROM _Job as j WITH (NOLOCK) 
INNER JOIN _Sent as se WITH (NOLOCK) on j.JobID = se.JobID 
INNER JOIN ENT._Subscribers as su WITH (NOLOCK) on se.SubscriberID = su.SubscriberID 
WHERE j.EmailID = 2521896 

Thanks Annon and Jim for helping me get started!

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.