SendLog woes, very strange behavior when trying to join with SendLog

Question

I have a very simple query

SELECT o.*,sl.Market,sl.Category 
FROM _Open o WITH (NOLOCK)
INNER JOIN SendLog sl WITH (NOLOCK)
ON o.JobID = sl.JobID
WHERE o.JobID IN (3103648)

Which is trying to get records from the _Open table and join them with the SendLog table to get Market and Category.

My Data Extension has 3 columns JobID, Market and Category.

The query above was more complex, but I stripped it down to the fundamentals, bare bones.

Yet that query is giving me 29+ million rows on a send that was only about 7k. I even filtered off of the SendLog for the Category column for the value from last night's send, and that yielded 29 million rows. So something is off. It seems like it's duplicating rows or something. My query seems correct, so I'm not sure what the deal is. For the record, I have NEVER had success querying the SendLog, I always have issues.

Answer 1

Opens are not 1:1 with Sends. Your subscribers can and typically do open an email multiple times after a send. Each open gets recorded when the tracking pixel is displayed.

I'd suggest restricting it with the unique flag:

select 
o.*
, sl.market
, sl.category 
from _Open o
inner join Sendlog sl on o.jobid = sl.jobid
where o.isunique = 1 
and o.jobid in (3103648)

Update

I get it now. Here's a query that should restrict the joins to common subscribers. I still think the isUnique = 1 is important:

select 
o.*
, sl.market
, sl.category 
from _Open o
inner join Sendlog sl on o.jobid = sl.jobid and o.subscriberid = sl.subid
where o.isunique = 1 
and o.jobid in (3103648)