QUERY CHALLENGE - FIND CUSTOMERS WHO HAVE SPECIFIC PRODUCTS

Database
Enthusiast

QUERY CHALLENGE - FIND CUSTOMERS WHO HAVE SPECIFIC PRODUCTS

Hi Everyone

 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

Cust_Id

10000 

Prod_Id

12

acct_id

1111234

Cust_Id

100000

Prod_id

35

acct_id

1111314

Cust_id

100000

Prod_id

12

acct_id

1258468

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.

3 REPLIES
Senior Apprentice

Re: QUERY CHALLENGE - FIND CUSTOMERS WHO HAVE SPECIFIC PRODUCTS

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)
);

SELECT cust_id
FROM tab
GROUP BY 1
HAVING CASE WHEN prod_id IN (11,12) THEN 1 ELSE -100 END > 0;
Enthusiast

Re: QUERY CHALLENGE - FIND CUSTOMERS WHO HAVE SPECIFIC PRODUCTS

Hi Dieter,

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.

Slice

Senior Apprentice

Re: QUERY CHALLENGE - FIND CUSTOMERS WHO HAVE SPECIFIC PRODUCTS

Hi Slice,

the outer WHERE filters for customers with either product 11 or 12 and the subquery removes them if they got any other product.