How can I use AND in Select query on the same field of same table?

[jitendramca]

I have one table "order_items", which contains user_id, product_id and some more fields. I want to found the users who have purchased three particular products having product_id e.g 101, 102, 103 (All Three Products not any of Three products)

Please help me...
How can I? :confused:

[aj_nsc]

select user_id from `order_items` where product_id = '101' or product_id='102' or product_id='103';


After this I'd just use whatever programming language you are using to only pull out the user_ids that show up 3 times in the result. I would imagine if you wanted to return it from a single query you'd have to use two subqueries and I'm not sure which is the better move performance-wise, although I'd imagine it would be the simple single query followed by using a server side language to sift through your results.

[jitendramca]

You have suggested me this :

select user_id from `order_items` where product_id = '101' or product_id='102' or product_id='103';

but from using this query I can find only the users who have purchase any of product_id 101 or product_id 102 or product_id 103.

but my problem is that I have to find out the users who have purchase all of those three products...(All not any):mad:


Please help!


Jitendra

[mattyblah]

select user_id from users_table ut
where exists
(select * from order_items oi where ut.user_id = oi.user_id and product_id = '101'
)
and exists
(select * from order_items oi where ut.user_id = oi.user_id and product_id = '102'
)
and exists
(select * from order_items oi where ut.user_id = oi.user_id and product_id = '103'
)

If that query doesn't meet proper syntax hopefully it will point you in the right direction.

[apulmca2k4]

I have a solution.

Edit: No personal contact information. Please feel free to post solutions on the board.

[chazzy]

even if it does, there are certain performance considerations to be worried about with using 3 subselects and 3 exists.

[felgall]

Possibly a join would be more efficient than the exists and subselects.

select u.user_id
from users_table u, order_items o1, order_items o2, order_items o3
where u.user_id = o1.user_id and u.user_id = o2.user_id and u.user_id = o3.user_id
and o1.product_id = '101' and o2.product_id = '102' and o3.product_id = '103'

[Nedals]

Assuming the following tables...

order_items: | product_id | user_id | .....
product_table: | product_id | product_name | ....
user_table: | user_id | user_name | .....

SELECT p.product_name, u.username FROM order_items d
LEFT JOIN product_table p ON p.product_id=d.product_id
LEFT JOIN user_table u ON u.user_id=d.user_id
WHERE d.product_id IN (101,102,103)