I have created a Query which will query for subscribers from a certain list who have not received an email which its name contains "newsletter_"

This is working perfect

SELECT l.[EmailAddress], e.[First_name]
FROM _Job j with (nolock)
INNER JOIN _Sent s with (nolock)
ON j.JobID = s.JobID
INNER JOIN _ListSubscribers l with (nolock)
ON l.subscriberID = s.subscriberID
INNER JOIN _EnterpriseAttribute e with (nolock)
ON l.subscriberID = e._subscriberID
WHERE s.EventDate < GetDate()
AND e.Segment = 'Customer'
AND j.EmailName NOT LIKE 'newsletter_%'
AND l.ListID = 919445

the problem is, is this is not bringing back subscribers who have never received and email at all as they are not in the _sent / _job does anyone know how i am able to add subscribers to this query who have never received an email

Thank you in advance

  • You have set the condition to j.EmailAddress NOT LIKE 'newsletter_%'. Doesnt this exclude them? – Samuel Dare May 25 '17 at 10:21
  • j.EmailName NOT LIKE 'newsletter_%' does not exclude them nope, as it cant find the subscribers in _job or _sent – odd May 25 '17 at 11:36

Could you make a separate query using a NOT EXISTS clause and then merge the results?

SELECT l.[Email_Address], e.[First_name]
FROM _ListSubscribers
  FROM _Sent 
  WHERE _Sent.[Email_Address] = _ListSubscribers.[Email_Address]
| improve this answer | |
  • ill give this ago and see if this works on its own – odd May 25 '17 at 11:09
  • 1
    the above working, thank you SELECT l.[EmailAddress], e.[First_name] FROM _ListSubscribers l with (nolock) INNER JOIN _EnterpriseAttribute e with (nolock) ON l.subscriberID = e._subscriberID WHERE NOT EXISTS (SELECT * FROM _Sent WHERE _Sent.[subscriberID] = l.[subscriberID]) – odd May 25 '17 at 11:23
  • thank you now i just need to figure out how to combine the two – odd May 25 '17 at 11:36

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.