Problem with EXISTS : Customers with atleast 3 specific products

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Apologize for the long posting

I have a table where I track products purchased by customers. I have a query to retrieve customers who have purchased at least 3 specific products (passed as parameter). I use EXISTS, but this brings back all customers who have at least one of the products (works like an IN clause

This is the table (sample)

CUSTOMER_ID PRODUCT_I
1 100
1 110
1 130
1 140
2 100
2 110
2 140

This is the Query

select * from test7
where (((product_id = (100
or product_id = (110)
or product_id = 130)) an
exists (select product_id from test7 b
where a.user_id = b.user_id
and product_id = 100
and exists (select product_id from test7 b
where a.user_id = b.user_id
and product_id = 110
and exists (select product_id from test7 b
where a.user_id = b.user_id
and product_id = 130)

Any help would be greatly appreciated

Thanks
R
 
PARAMETERS P1 LONG, P2 LONG, P3 LONG;
SELECT .... FROM CUST A
WHERE EXISTS (SELECT 'X' FROM PURCH B, PURCH C, PURCH D
WHERE B.CUSTID = A.CUSTID
AND B.PRODID= P1
AND C.CUSTID = A.CUSTID
AND C.PRODID= P2
AND D.CUSTID = A.CUSTID
AND D.PRODID= P3)

HTH

Pieter

Need help said:
Apologize for the long posting.

I have a table where I track products purchased by customers. I have a
Click to expand...
query to retrieve customers who have purchased at least 3 specific products
(passed as parameter). I use EXISTS, but this brings back all customers who
have at least one of the products (works like an IN clause)
 
Thanks Pieter. Impatient person that I am, I created a workaround. I insert all customers who brought at least ONE product into a temporary table, and then delete the ones that didnt buy any of the other products one product at a time. I then created a report based on the temporary table

Looks like I re-invented the wheel on this one. The perfomance on my method seems to be satisfactory (2 minutes for macro to run and report to launch): the base tables have several hundred thousand rows with exquisitely complex joins.
 
Back
Top