Sql query to exclude related records in dataset

Question

I am attempting to create an sql query in salesforce marketing cloud...I have a list of email addresses, the number of products they own, and the associated product codes. I would like to set up the query logic so that if someone has multiple products, and one of those products is product 'A', I would like to exclude those email records. In the screenshot is a sample of the data format - if a contact has two products, that contact has has two records in the dataset broken out by each product code, and so on. I have attempted to run this exclusion using the sql below - the result is it excludes the record with product A, but includes the record with product B. My goal is to have the query exclude both records A & B (all records associated to that excluded email address), not A only. Can marketing cloud fulfill this need using sql query/automation studio, and what sql logic would be needed (i.e. is there a function that would allow the product codes to be combined from two rows into comma separated values in one row)... is there any sort of workaround that would solve for this?

select email_address from 'table' where 1=1 and ((product count > 1 and product code not in ('A'))

Answer 1

I think you can do that with a 'not exists' clause.

Something like:

SELECT a.email_address
FROM [table] a
WHERE NOT EXISTS (
  SELECT TOP 1 email_address
  FROM [table] b
  WHERE b.product_code = 'A'
  AND a.email_address = b.email_address
)

This would select all records from table that do not have a record with a product_code of A.

The NOT EXISTS will pull up any records with A and then as it is NOT EXISTS, will send a false result, meaning that record will not be included in the final data set.