I have a query, I want to bring in all the Sends (for particular email) and opens. But in my query it only brings the matching Opens. For example email sent to 1000 subscribers and total unique opens are 100, thus query returning 100 records. I want query should return 1000 records and then wherever subscriber matches for open it should pull in else let the field be blank.
Here is my query, (i already tried LEFT vs RIGHT join in 2nd join which is Open and send.
SELECT s.SubscriberKey as Email, J.EmailName, S.EVENTDATE AS SendDate, O.Eventdate as OpenDate, C.Eventdate as ClickDate FROM [_Sent] S LEFT JOIN [_Job] J ON S.JobID = J.JobID LEFT Join [_Open] O ON S.AccountID = O.AccountID AND S.OYBAccountID = O.OYBAccountID AND S.ListID = O.ListID AND S.BatchID = O.BatchID AND S.JOBID = O.JOBID and S.SubscriberID = O.SubscriberID AND S.SubscriberKey = O.SubscriberKey LEFT Join [_CLICK] C ON S.AccountID = C.AccountID AND S.OYBAccountID = C.OYBAccountID AND S.ListID = C.ListID AND S.BatchID = C.BatchID AND S.JOBID = C.JOBID and S.SubscriberID = C.SubscriberID AND S.SubscriberKey = C.SubscriberKey WHERE j.emailname = 'onboarding_phase_1_1_v2' and (o.isunique = 1 or o.isunique is null) and (C.isunique = 1 or C.isunique is null) and cast(s.EventDate As Date) = cast('2019-06-11' as Date)