Query Subscribers with No Opens from Publication List

Question

I'm trying to select subscribers who haven't opened any email sent to a publication list over the last 6 months. Below is what I've tried, but it returns subscribers who didn't open from a single send, returning multiple rows with the same subscriber and different JobID's.

Select distinct
s.SubscriberKey as EmailAddress,
s.JobID,
s.BatchID,
convert(char(19),s.EventDate,20) as SendDate
from [_sent] s
left join [_open] o
on s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and 
s.SubscriberID = o.SubscriberID and o.IsUnique = 1
where
s.ListID = 5135643
and o.SubscriberID is NULL

Update: Got it to work by removing the JobID, ListID, and BatchID from the join.

Select distinct
s.SubscriberKey as EmailAddress
from [_sent] s
left join [_open] o on s.SubscriberID = o.SubscriberID
where
s.ListID = 5135643
and o.SubscriberID is NULL

Answer 1

Its a bit late, so I may be a little off, but try something like:

Select s.SubscriberKey as EmailAddress,
s.JobID,
s.BatchID,
convert(char(19),s.EventDate,20) as SendDate
from [_sent] s
WHERE NOT EXISTS(
  SELECT o.SubscriberKey
  FROM [_open] o
  WHERE o.ListID = 5135643
)
AND s.ListID = 5135643

That should give you those that were sent and used the specific listid, but did not open it (via NOT EXISTS subquery to _open)

It is a very similar query, but you could also try:

Select s.SubscriberKey as EmailAddress,
s.JobID,
s.BatchID,
convert(char(19),s.EventDate,20) as SendDate
from [_sent] s
LEFT JOIN [_open] o
ON o.SubscriberKey = s.SubscriberKey
WHERE o.ListID = 5135643
AND s.ListID = 5135643
AND o.SubscriberKey IS NULL