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