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
1
  • 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
    – 0xsegfault
    May 19, 2017 at 20:55

2 Answers 2

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
6
  • But the JOB ID is constantly changing. This is for a welcome email sent daily. So would this still fix it?
    – Emily
    May 22, 2017 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, 2017 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, 2017 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) May 23, 2017 at 2:18
  • Its still not working with the automation either. No errors, just nothing populates into the data extension.
    – Emily
    May 23, 2017 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!

You must log in to answer this question.

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