Getting all sends, and (unique) opens and clicks into one Data Extension

Question

We would like to get the sends, (unique) opens and (unique) clicks from last 24 hours in the same data extension. I started with 2 LEFT JOINS, with the goal of getting all sends, and opens and clicks only where they ocurred. However, I'm stuck because the results are having lots of duplicates (the target data extension does not have a primary key to avoid any PK violations). I followed the data structure as described on SFMC data views help page. This is the query we are using (separately for all three metrics):

Sends

SELECT a.subscriberkey, CONVERT(varchar, EventDate, 20) AS EventDate,
b.emailid, b.emailname, CONCAT(a.Subscriberkey,'_Sent_',b.EmailID,'_',convert(varchar, EventDate, 20) ) AS Event_Id 
FROM _Sent AS a 
INNER JOIN _job AS b ON a.jobid=b.jobid AND 
CAST(a.eventdate as date)>=cast(getdate() -1 as date)

Opens

SELECT a.subscriberkey, CONVERT(varchar, EventDate, 20) AS EventDate, 
b.emailid, b.emailname, CONCAT(a.Subscriberkey,'_Open_',b.EmailID,'_',convert(varchar, EventDate, 20) ) AS Event_Id 
FROM _open AS a 
INNER JOIN _job AS b ON a.jobid=b.jobid 
AND CAST(a.eventdate as date)>=cast(getdate() -1 as date) WHERE a.IsUnique = 1

Clicks

SELECT a.subscriberkey, CONVERT(varchar, EventDate, 20) AS EventDate, b.emailid, 
b.emailname, a.LinkContent AS URL, a.LinkName AS Alias, a.LinkContent AS Details,CONCAT(a.Subscriberkey,'_Click_',
b.EmailID,'_',convert(varchar, EventDate, 20) ) AS Event_Id 
FROM _click AS a 
INNER JOIN _job AS b ON a.jobid=b.jobid 
AND CAST(a.eventdate as date)>=cast(getdate() -1 as date) 
WHERE a.IsUnique = 1

Answer 1

If I understand correctly that you basically want a "list of events" in your data extension, regardless of type, I would do the following:

Write three separate queries into three DEs. You already have those queries listed, if I'm not mistaken.

(i call the three resulting DEs "sendevents", "openevents", "clickevents" below).

Next: UNION the three resulting DEs, setting fields that don't exist in the respective DE to null, like so:

SELECT 
sent.subscriberkey, 
sent.EventDate,
sent.emailid,
sent.emailname, 
sent_Event_Id,
null AS LinkContent,
null AS Alias
/* ... go through all fields that exist in any of the three DEs */
FROM Sendevents

UNION

SELECT
sent.subscriberkey, 
sent.EventDate,
sent.emailid,
sent.emailname, 
sent_Event_Id,
null AS LinkContent,
null AS Alias
/* ... go through all fields that exist in any of the three DEs */
FROM openevents


UNION 

SELECT
sent.subscriberkey, 
sent.EventDate,
sent.emailid,
sent.emailname, 
sent_Event_Id,
null AS LinkContent,
null AS Alias
/* ... go through all fields that exist in any of the three DEs */
FROM clickevents

Does this help you?