0

In Marketing Cloud, we would like to check for the existence of records from one table in multiple other tables based on the unique identifier - Customer_ID. However, I'm not sure whether I can include multiple SELECT statements and WHERE clause with multiple filters. I'm not getting any results in QueryStudio. Here's my query

SELECT a.Customer_ID
FROM email_audience AS a
WHERE EXISTS (
    SELECT t1.Customer_ID
    FROM table_1 AS t1
    UNION
        SELECT t2.Customer_ID
        FROM table_2 AS t2
    UNION
        SELECT t3.Customer_ID
        FROM table_3 AS t3
    UNION
        SELECT t4.Customer_ID
        FROM table_4 AS t4
    WHERE a.Customer_ID = t1.Customer_ID
        OR a.Customer_ID = t2.Customer_ID
        OR a.Customer_ID = t3.Customer_ID
        OR a.Customer_ID = t4.Customer_ID
)
1

Assuming the Customer ID could be duplicated if they exist in any of the tables, and you only want a single record returned if there is a match - the following should work:

SELECT 
a.Customer_ID
FROM email_audience AS a
INNER JOIN (
    SELECT DISTINCT
    b.Customer_ID
    FROM (
        SELECT t1.Customer_ID
        FROM table_1 AS t1
    UNION
        SELECT t2.Customer_ID
        FROM table_2 AS t2
    UNION
        SELECT t3.Customer_ID
        FROM table_3 AS t3
    UNION
        SELECT t4.Customer_ID
        FROM table_4 AS t4
    ) b 
) c ON a.Customer_ID = c.Customer_ID

You could also do this with a series of LEFT JOIN IS NOT NULL checks:

SELECT DISTINCT
a.Customer_ID
FROM email_audience AS a
LEFT JOIN [table_1] t1 ON t1.Customer_ID = a.Customer_ID
LEFT JOIN [table_2] t2 ON t2.Customer_ID = a.Customer_ID
LEFT JOIN [table_3] t3 ON t3.Customer_ID = a.Customer_ID
LEFT JOIN [table_4] t4 ON t4.Customer_ID = a.Customer_ID
WHERE (t1.Customer_ID IS NOT NULL 
OR t2.Customer_ID IS NOT NULL 
OR t3.Customer_ID IS NOT NULL 
OR t4.Customer_ID IS NOT NULL)

I hope that helps.

| improve this answer | |
  • Cameron you're a star :-) One question just for my understanding: why WHERE EXISTS does not work here? I got the results when I applied EXISTS to a single table in the subquery, but it did not work with multiple tables/UNIONS. – nix9247 Oct 17 '19 at 4:25
  • I've had issues using the EXISTS operator in SFMC; conducting JOINs in the methods shown above has been more reliable in my experience. It shouldn't have any material difference in the results, and unless these tables have millions upon millions of rows - you shouldn't have any performance concerns. – Cameron Robert Oct 17 '19 at 4:34
  • Ok. Thanks for clarifying. – nix9247 Oct 17 '19 at 4:45

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.