we have two Data Extensions - DE1 one storing client's data and another one, DE2, storing information on with a 1: many relationship. We now need to filter on exactly one of the values DE2 Data. Example: DE2 stores products and now I want to filter on everyone from DE1 who has only product no3 in place, not any other. When filtering with a regular DE it shows everyone where the product applies to, even if the contact is related to several other ones. Is there a way using queries? Thanks!
This can be done with a SQL activity in Automation Studio. Left join your DE2 to DE1 with any records that meet the where clause, both products 1 and 2 are null. I'm assuming you have SubscriberKey as the unique identifier, this can also be a customer number or email address.
select a.SubscriberKey, b.ProductNo3 from DE1 a left join DE2 b on (a.SubscriberKey = b.SubscriberKey) where b.ProductNo1 is null and b.ProductNo2 is null