Query data extension with multiple columns to find the products that don't exist in another data extension

Question

I have this two Data Extensions and the ProductCode is the same as Product1, Product2, Product3, Product4...Product10

The problem is that there is some Products in Product1, Product2...Product10 Data Extension that don't exist in ProductCode Data extension and I want to create a list with this products but I can't think of any way of doing that.

I tried to create 10 queries joining the Second Data Extension on Product1 = ProductCode and use the union function but I don't think that's the best way.

I hope this makes sense. If you have better ways of doing this please share

Answer 1

The simplest way would be to conduct 10 LEFT JOIN IS NULL checks - one for each product - and union them all together.

SELECT
customerid
,productId1 AS 'Product'
,'productId1' AS 'Source'
FROM [Data Extension 1] a
LEFT JOIN [Products] b ON a.productId1 = b.ProductCode
WHERE b.ProductCode IS NULL

UNION

SELECT
customerid
,productId2 AS 'Product'
,'productId2' AS 'Source'
FROM [Data Extension 1] a
LEFT JOIN [Products] b ON a.productId2 = b.ProductCode
WHERE b.ProductCode IS NULL

UNION

SELECT
customerid
,productId3 AS 'Product'
,'productId3' AS 'Source'
FROM [Data Extension 1] a
LEFT JOIN [Products] b ON a.productId3 = b.ProductCode
WHERE b.ProductCode IS NULL

...and so on until productId10...
Change [Data Extension 1] to be the name of your DE with CustomerID; it wasn't listed in your question.

Output DE Structure:
customerid | Product | Source

This gives you a 3 column DE with the CustomerID, the ProductCode that could not be found in the Products DE, and column called "Source" that tells you what ProductID# the missing product was in - in case you need this.

You should be able to export this Data into a CSV to determine:

1. The list of Products that are missing
2. The customers affected by missing products
3. The ProductID# columns that missing products are found in.

Another way would be to pivot your 10 products into 1 table, and then conduct a single LEFT JOIN IS NULL:

SELECT
    customerid
    ,Product
    ,Source
FROM (
    SELECT customerid,productId1 AS 'Product','productId1' AS 'Source'
    FROM [Data Extension 1]
    UNION
    SELECT customerid,productId2 AS 'Product','productId2' AS 'Source'
    FROM [Data Extension 1]
    UNION
    SELECT customerid,productId3 AS 'Product','productId3' AS 'Source'
    FROM [Data Extension 1]
    UNION
    SELECT customerid,productId4 AS 'Product','productId4' AS 'Source'
    FROM [Data Extension 1]
    UNION
    SELECT customerid,productId5 AS 'Product','productId5' AS 'Source'
    FROM [Data Extension 1]
    UNION
    SELECT customerid,productId6 AS 'Product','productId6' AS 'Source'
    FROM [Data Extension 1]
    UNION
    SELECT customerid,productId7 AS 'Product','productId7' AS 'Source'
    FROM [Data Extension 1]
    UNION
    SELECT customerid,productId8 AS 'Product','productId8' AS 'Source'
    FROM [Data Extension 1]
    UNION
    SELECT customerid,productId9 AS 'Product','productId9' AS 'Source'
    FROM [Data Extension 1]
    UNION
    SELECT customerid,productId10 AS 'Product','productId10' AS 'Source'
    FROM [Data Extension 1]
) a
LEFT JOIN [Products] b ON a.Product = b.ProductCode
WHERE b.ProductCode IS NULL
AND a.Product IS NOT NULL

Again, change [Data Extension 1] to be the correct DE name.

The Output is the same as the first option; 3 columns to help you find the missing products and affected customers.