I am trying to find a way to return client no's that exclusively have a product(s). Let's say the product numbers are 1 and 2. They customer can either have product_id 1 or product_id 2 or both. Now the problem is that many customers may either of these products but also other products. These customers have to be excluded.
The relationship between customers and products is many to many.
So for example in the Table Cust_Prod_Reltn
This customer would not be desired because he/she has a product 35. I need to find only customers with either product_id 11 or product_id 12 in their portfolios.
SELECT * FROM tab AS t1
WHERE prod_id IN (11,12)
AND NOT EXISTS
SELECT * FROM tab AS t2
WHERE t1.cust_id = t2.cust_id
AND t2.prod_id NOT IN (11,12)
GROUP BY 1
HAVING CASE WHEN prod_id IN (11,12) THEN 1 ELSE -100 END > 0;
But does this query exclusively find the customers that have products 11 or 12 and nothing else? It seems that within the subquery you are just seeking customers that do not have products 11 or 12 at all. What about the scenario when a customer could have 11, 12, and other products. These would need to be eliminated from the results.
the outer WHERE filters for customers with either product 11 or 12 and the subquery removes them if they got any other product.