When I select from _Sent and LEFT JOIN _Job it returns null values for fields selected from _Job.
I'm querying from the Parent BU so this shouldn't be the BU specific issue.
SELECT
s.SubscriberKey,
j.EmailName,
j.EmailSubject,
jy.JourneyName,
jy.VersionNumber,
s.EventDate AS event_date,
'Sent' as event_type
FROM _Sent s
LEFT JOIN _Job j ON s.JobID = j.JobID
LEFT JOIN _JourneyActivity ja ON j.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
LEFT JOIN _Journey jy ON ja.VersionID = jy.VersionID
WHERE
s.EventDate > dateadd(hh,-24,getdate()) and jy.JourneyID = 'xxxxxxxxxxxxxx'
But when I flip the select from _Sent to _Job, and LEFT JOIN _Sent, it doesn't return null values.
SELECT
s.SubscriberKey,
j.EmailName,
j.EmailSubject,
jy.JourneyName,
jy.VersionNumber,
s.EventDate AS event_date,
'Sent' as event_type
FROM _Job j
LEFT JOIN _Sent s ON s.JobID = j.JobID
LEFT JOIN _JourneyActivity ja ON j.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
LEFT JOIN _Journey jy ON ja.VersionID = jy.VersionID
WHERE
s.EventDate > dateadd(hh,-24,getdate()) and jy.JourneyID = 'xxxxxxxxxxxxxx'
What am I doing wrong?
Should I always be selecting from _Job first then joining the other engagement data views. E.g Click, Open etc.
Thanks.