Marketing Cloud - combine results from 3 tables

Question

I need to combine results from three different Data Extensions that contains the same set of columns. For each SubscriberKey I'd like to see the Clicks_[Country] and Rating_[Country] even if they have null value.

For most of the records it works fine but for some records SubscriberKey and personal data are missing. Could you please help me solve it?

SELECT
ukr.SubscriberKey as SubscriberKey,
ukr.FirstName,
ukr.LastName,
ukr.Email,
ukr.Clicks as Clicks_Ukraine,
ukr.Rating as Rating_Ukraine,
syr.Clicks as Clicks_Syria,
syr.Rating as Rating_Syria,
afg.Clicks as Afghanistan_Clicks,
afg.Rating as Afghanistan_Rating
FROM Total_Clicks_Ukraine_V2 ukr
FULL OUTER JOIN Total_Clicks_Syria_V2 syr 
ON ukr.SubscriberKey = syr.SubscriberKey

FULL OUTER JOIN Total_Clicks_Afghanistan_V2 afg 
ON ukr.SubscriberKey = afg.SubscriberKey

Answer 1

Try a left join instead of a full outer join:

SELECT
ukr.SubscriberKey as SubscriberKey,
ukr.FirstName,
ukr.LastName,
ukr.Email,
ukr.Clicks as Clicks_Ukraine,
ukr.Rating as Rating_Ukraine,
syr.Clicks as Clicks_Syria,
syr.Rating as Rating_Syria,
afg.Clicks as Afghanistan_Clicks,
afg.Rating as Afghanistan_Rating
FROM Total_Clicks_Ukraine_V2 ukr
LEFT JOIN Total_Clicks_Syria_V2 syr 
ON ukr.SubscriberKey = syr.SubscriberKey
LEFT JOIN Total_Clicks_Afghanistan_V2 afg 
ON ukr.SubscriberKey = afg.SubscriberKey

The FULL OUTER JOIN keyword returns all records when there is a match in left or right table records, while the LEFT JOIN keyword returns all records from the left table, and the matching records from the right table.

Source: https://www.w3schools.com/sql/sql_join_full.asp, https://www.w3schools.com/sql/sql_join_left.asp