Query Data Extension with Left Join

Question

I have one data extension that is about 9 million records (IGO 3 months simple). It has 2 columns, SKU and CustomerID. Both of the fields are NULL so there is duplicated CustomerID's and SKU's.

I'm trying to left join another two data extensions.

First one (Unique ID Repository with Customer ID as PK) that is only about 3 million records.

Second one(Product Name) just over 2 million, where ProductDescriptionID is the PK and ProductName Nullable.

If I run the query with only the first join, I get the 9 million records back. When I join the second one, I get over 40 million records and I don't understand how is this possible when the DE that I'm joining only has just over 2 million records?

SELECT
igo.CustomerID,
igo.sku,
ui.ProductDescriptionID,
pn.ProductName

from [IGO 3 months simple] igo

left join [Unique ID Repository] ui
on igo.CustomerID = ui.CustomerID

left join [Product Name] pn
on ui.ProductDescriptionID = pn.ProductDescriptionID

Can someone explain me how is this possible please? If you need more info please let me know.

Answer 1

Try:

SELECT
igo.CustomerID,
igo.sku,
ui.ProductDescriptionID,
pn.ProductName

from [IGO 3 months simple] igo

left join [Unique ID Repository] ui
on igo.CustomerID = ui.CustomerID

left join [Product Name] pn
on ui.ProductDescriptionID = pn.ProductDescriptionID
WHERE ui.ProductDescriptionID is not null AND igo.CustomerID IS NOT NULL

Your ProductDescriptionID in Unique ID Repository is nullable, so I think it might be leading to comparison problems in:

left join [Product Name] pn
on ui.ProductDescriptionID = pn.ProductDescriptionID

Answer 2

It sounds like you have 2 Data Extensions with unique information (Customers and Product), and you have 1 Data Extension with "transaction" information. Based on your question, it looks like you are trying to enrich your 9 million "transactions" (product views) with data from the Customer and Product DEs.

Assuming that this is your table relationship:

If this is correct - try the following:

SELECT
ui.CustomerID
,igo.sku
,ui.ProductName
FROM [IGO 3 months simple] igo
LEFT JOIN [Unique ID Repository] ui ON igo.CustomerID = ui.CustomerID
LEFT JOIN [Product Name] pn ON igo.sku = pn.ProductDescriptionID
WHERE igo.CustomerID IS NOT NULL
AND pn.ProductDescriptionID IS NOT NULL

This will only return Non-NULL CustomerID records from IGO that exist in UniqueIDRepository, and where the SKU they viewed exists in [Product Name]. It will return multiples for the same customer (if they have multiple entries in IGO) since it is a one-to-many relationship.

Using the sample data in the image above, the output for this query should be: