Query to Combine Data from 3 different Data View Tables

Question

I've pulled the last 30 days of data from 3 data view tables (Job, Open, and Click) into 3 different data extensions. I'm attempting to run a query to dump data from my 3 different data extensions into a single data extension. I'm looking to take data from the JobDataView, OpenDataView, and ClickDataView data extensions and put the below data from them into a single data extension, something like this:

JobDataView	OpenDataView	ClickDataView
JobID	JobID	JobID
EmailID	SubscriberKey	EventDate
EmailName	EventDate	URL
EmailSubject		LinkName
FromName		LinkContent
FromEmail

This is what my current query is:

SELECT JobDataView.JobID, 
JobDataView.EmailID, JobDataView.EmailName, 
JobDataView.EmailSubject, JobDataView.FromName, 
JobDataView.FromEmail, OpenDataView.SubscriberKey,
OpenDataView.EventDate, ClickDataView.URL, 
ClickDataView.LinkName, ClickDataView.LinkContent
FROM JobDataView
FULL JOIN OpenDataView ON OpenDataView.JobID = JobDataView.JobID
FULL JOIN ClickDataView ON ClickDataView.JobID = JobDataView.JobID

It seems to only pull in data for SubscriberKey and EventDate, everything else is showing null in query studio. I'm trying to get all of the data points in my table above into a single data extension.

How can I get my query to pull all of the data points above into a single data extension?

Answer 1

Considerations:

1. Source Data Extensions do have info in the mentioned fields since you pull the data not directly from the Data Views but from respective Data Extensions
2. Target Data Extension has a fully correct configuration to retrieve mentioned fields (e.g. Primary Keys, non-nullable fields, and fields' length)

Updated SQL:

SELECT
JobDataView.JobID, 
JobDataView.EmailID,
JobDataView.EmailName, 
JobDataView.EmailSubject,
JobDataView.FromName, 
JobDataView.FromEmail,
OpenDataView.SubscriberKey,
OpenDataView.EventDate AS OpenEventDate,
ClickDataView.EventDate AS ClickEventDate,
ClickDataView.URL, 
ClickDataView.LinkName,
ClickDataView.LinkContent
FROM JobDataView
LEFT JOIN OpenDataView ON OpenDataView.JobID = JobDataView.JobID
LEFT JOIN ClickDataView ON ClickDataView.JobID = JobDataView.JobID
WHERE
JobDataView.CreatedDate >= Convert(date, GetDate()-30) AND
JobDataView.CreatedDate < Convert(date, GetDate())

Notes:

• You can have both EvenDates from Open and Click DEs by simply naming them differently
• There is a difference between Full and Left Joins, in your case, it would be sufficient enough to use Left one
• I have included the WHERE clause with the CreatedDate field from Job DE to select the last 30 days (feel free to specify another Date field for filtering)