Results 1 to 4 of 4

Thread: How to do this?

  1. #1
    Join Date
    Sep 2002
    Posts
    33

    Unanswered: 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,

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    where CarID between 10 and 30

    otherwise

    where CarID = 10 and CarID = 20 and CarID = 30 and CarID = 10000001
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Sep 2002
    Posts
    33
    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 :/

  4. #4
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •