CODEWITHSUNDEEP
mysqlsql
Teja
Teja

Reputation: 13534

Get all customers who purchased three specified items within the same order

I have a customer_orders table as below and I would like to retrieve all the customers who purchases items 'x','y','z' in the same order. Just wondering how to implement the solution for this problem.

Input Data :-

CREATE TABLE customer_orders
(
  customer_id INTEGER,
  order_id INTEGER,
  item_id INTEGER,
  ORDER_QTY INTEGER,
  PRICE INTEGER,
  ORDER_DATE DATE
 ); 

 INSERT INTO customer_orders VALUES(101,901,1,50,10,'2015-07-15'); 
 INSERT INTO customer_orders VALUES(101,901,2,20,50,'2015-07-15');
 INSERT INTO customer_orders VALUES(101,901,3,40,30,'2015-07-15'); 
 INSERT INTO customer_orders VALUES(102,902,2,30,15,'2015-04-16');
 INSERT INTO customer_orders VALUES(102,902,3,20,20,'2015-04-16');
 INSERT INTO customer_orders VALUES(103,903,1,50,10,'2014-05-17'); 
 INSERT INTO customer_orders VALUES(102,904,3,100,20,'2014-08-18'); 
 INSERT INTO customer_orders VALUES(101,905,2,200,15,'2014-09-19'); 
 INSERT INTO customer_orders VALUES(104,906,3,70,20,'2015-10-18'); 
 INSERT INTO customer_orders VALUES(104,906,2,30,15,'2015-10-18'); 
 INSERT INTO customer_orders VALUES(104,906,1,40,10,'2015-10-18'); 
 INSERT INTO customer_orders VALUES(105,907,2,80,15,'2014-03-29'); 
 INSERT INTO customer_orders VALUES(104,908,2,90,15,'2014-05-19');

Output :-

101
104

Assumption :- Item x --> 1, Item y ---> 2, Item z --> 3

Upvotes: 2

Views: 46

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

Your solution doesn't actually solve the problem. You need to group your results by orders as well not to mix up ordered items across different orders and then retrieve unique customers:

SELECT DISTINCT 
         customer_id
FROM     customer_orders
WHERE    item_id BETWEEN 1 AND 3
GROUP BY customer_id, order_id
HAVING   COUNT( DISTINCT item_id ) = 3

For example, if you remove one of the items from the list (1,2,3) (eg: below) from one order for user 101 then in your solution it would still qualify user 101 in the output, because he has ordered item 2 in a different order. This behaviour would disqualify your approach.

-- INSERT INTO customer_orders VALUES(101,901,2,20,50,'2015-07-15');

See modified SQL Fiddle to see the problem I'm mentioning.

Upvotes: 3

Related Questions