All Forums Database
CCSlice 36 posts Joined 03/14
28 Aug 2014
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.

dnoeth 4628 posts Joined 11/04
28 Aug 2014

 

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;

 

Dieter

CCSlice 36 posts Joined 03/14
28 Aug 2014

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

dnoeth 4628 posts Joined 11/04
29 Aug 2014

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.

Dieter

You must sign in to leave a comment.