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

jitendramca · #1 (**permalink**) 05-09-08, 07:05

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?

Jitendra

healdem · #2 (**permalink**) 05-09-08, 07:40

use aliases

Id expect a query with 3 left joins would do the job, although there may well be a better solution...

Code:

select OI.User_ID, OI.Product_ID,........ from Order_Items as OI
left join order_items as A on A.User_ID=oi.User_ID
left join order_items as B on B.User_ID=oi.User_ID
left join order_items as C on C.User_ID=oi.User_ID
where A.Product_ID=101
and B.Product_ID=102
and C.Product_ID=103
and a.user_id != NULL
and b.user_id != NULL
and c.user_id != NULL
order by OI.User_ID

now whether it works or not I haven't got a clue.....

the left join should extract all records from OI, and those that match in the alisases A,B & C
where the product code is the required product code in A,B & C
AND where the use_ID in A, B & C is not NULL (ie there is something in the User_ID for each of the aliases, which we know if its present will be the same as the original table OI because of the join definition

it should work.. but it'll probably get slated by the knowledgeable ones here

you may need to check the not equals sign in MySQL,
likewise you may need to check if there is a better way of expressing comparisions to NULL using a function

r937 · #3 (**permalink**) 05-09-08, 08:23

Code:

SELECT user_id
  FROM order_items
 WHERE product_id IN ( 101,102,103 )
GROUP
    BY user_id
HAVING COUNT(*) = 3

mike_bike_kite · #4 (**permalink**) 05-09-08, 08:58

Rudy, your example hardly compares to healdem's NZDF classic.

Healdem, the != sign works fine in MySQL but only with non NULL values, you'd be best to use "is not NULL" here.