How does Sql exclude related records in the dataset?
I am attempting to create an sql query in the 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 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 fulfil 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'))
I think you can have Sql exclude related records in the dataset 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 tables 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.