How to do this?

I know how to use IN with WHERE clause but it does the OR on all values in the subquery. The question is how to make it match against all values returned?

ex:

SELECT UserID FROM UserCars WHERE CarID IN (10,20,30)

would return user ids of all users that have a either car 10, 20 OR 30

How to write simple query to get users that have cars 10, 20 AND 30

thanks,where CarID between 10 and 30

otherwise

where CarID = 10 and CarID = 20 and CarID = 30 and CarID = 10000001|||hmm, so there is really no other statement like IN that would do AND instead of OR on subquery?

i tried using ALL but that didnt work :/|||select u.UserID
from Users u
where exists(select 'x' from UserCars uc1 where u.UserID=uc1.UserID and uc1.CarID=10)
and exists(select 'x' from UserCars uc2 where u.UserID=uc1.UserID and uc1.CarID=20)
and exists(select 'x' from UserCars uc2 where u.UserID=uc1.UserID and uc1.CarID=30)

OR

select UserID
from UserCars
where CarID in (10,20,30)
group by UserID
having count('x')=3