CODEWITHSUNDEEP
mysqlsqljpql
edeesan
edeesan

Reputation: 354

Filter customers based on products they bought

I have 3 tables:

A row of customer_order contains a customer and a product that customer bought in that order.

Now for a management system, I have to input a list of products and find a list of customers who have bought all these products (inclusive). By inclusive I mean, all those customers that are filtered must have bought all the products.

Things to keep in mind:

In mysql I have this working with something like this in the customer_order table: 

select customer_id from  (SELECT count(distinct (product_id)) as prodcount, customer_id FROM customer_order where product_id = :prod_id1 or product_id = :prod_id2 group by customer_id) as temp where temp.prodcount= 2;

Here number of product_id is variable: prod_id1, prod_id2, prod_id3, and so on. In the temp.prodcount= 2, '2' is the number products as input.

I am counting the distinct number of products bought by a customer from the customer_order table after filtering the orders by input product list (where clause). And then comparing the count with the number of products input. If they are equal it means this is the customer who has bought all the products that are input

I am not sure this is the correct way of doing this as it seems more like a hack. Moreover, I need to write in JPQL. So a JPQL solution would be helpful.

Upvotes: 0

Views: 251

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Your approach is fine, but you can simplify your query by removing the subquery and using in:

select co.customer_id 
from customer_order co
where co.product_id in (:prod_id1, :prod_id2)
group by co.customer_id
having count(distinct product_id) = 2;

Upvotes: 2

Related Questions