1

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.

  • Hi @Duicug :) what are the column names in the second DE? Is CustomerID the primary key in both of them? – zuzannamj Oct 15 '19 at 10:21
  • Hi Zuzann :) CustomerID(PK), and ProductDescriptionID(Nullable) – Duicug Oct 15 '19 at 10:22
  • OK, and in IGO 3 DE - which column in PK? – zuzannamj Oct 15 '19 at 12:16
  • none, both of them are nullable, CustomerID and SKU – Duicug Oct 15 '19 at 12:21
  • 1
    really sorry @zuzannamj, I have updated my question. I was missing the second join. With the first joinI get the 9 million records when selecting the ProductDescriptionID only. The over 40 million comes from the second left – Duicug Oct 15 '19 at 14:17
0

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
| improve this answer | |
0

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: enter image description here

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: enter image description here

| improve this answer | |
  • really sorry, I have updated my question. I was missing the second join. With the first join I get the 9 million records when selecting the ProductDescriptionID only. The over 40 million comes from the second left – Duicug Oct 15 '19 at 14:16
  • tried pn.ProductDescriptionID is NOT NULL and ui.ProductDescriptionID but doesn't make any difference – Duicug Oct 15 '19 at 14:18
  • @Duicug, I've updated my answer to reflect the new information you provided. – Cameron Robert Oct 16 '19 at 0:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.