#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    8
    Rep Power
    0

    Exclamation Multiple OR using IN - Help


    Can anyone help me understand why this isn't working?

    I'm attempting to say:

    "Please MySQL Database, for the love of god, return me the rows that match any of these userid's and are associated with these assigncodes id and fall between these dates."

    This is what I'm writing:

    SELECT * FROM TBL_SCHED_MASTER WHERE USERID IN (11597, 11598, 11599, 11607, 11608, 11609, 11610, 11618, 11620, 11629, 11630, 11631, 11642, 11650, 11651, 11652, 12944, 21760) AND ASSIGNCODEID IN (963, 873, 871, 1178, 869) AND ASSIGNDATE BETWEEN '2013-03-01' and '2013-03-31';

    And it's telling me to go diaf.

    What have I done wrong?


    Best,
    Tanner Campbell
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    8
    Rep Power
    0
    PS: I'm very new to MySQL and still very much learning ... perhaps seeing what I had typed originally would help you understand what I'm trying to accomplish:

    Code:
    DELETE FROM TBL_SCHED_MASTER 
    WHERE USERID = 11597
    OR USERID = 11598
    OR USERID = 11599
    OR USERID = 11607
    OR USERID = 11608
    OR USERID = 11609
    OR USERID = 11610
    OR USERID = 11618
    OR USERID = 11620
    OR USERID = 11629
    OR USERID = 11630
    OR USERID = 11631
    OR USERID = 11642
    OR USERID = 11650
    OR USERID = 11651
    OR USERID = 11652
    OR USERID = 12944
    OR USERID = 21760
    AND
    ASSIGNCODEID = 963
    OR ASSIGNCODEID = 873
    OR ASSIGNCODEID = 871
    OR ASSIGNCODEID = 1178
    OR ASSIGNCODEID = 869
    AND
    ASSIGNDATE BETWEEN '2013-03-01' AND '2013-03-31';
    Slightly embarrassing, really.
  4. #3
  5. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Why do you think your IN() query is wrong?

    Based on the description you give:
    -"... return me the rows that match any of these userid's and are associated with these assigncodes id and fall between these dates."
    That query is correct.



    You OR ... OR .... AND ... OR ... version of the query on the other hand is not correct and will show much more rows than you actually want.
    When you work with a bit more complex query with OR's and AND's I recommend using parenthesis to group the expressions explicitly and not rely on the operator precedence.
    Your query is essentially written like this:
    Code:
    DELETE FROM TBL_SCHED_MASTER 
    WHERE USERID = 11597
    OR USERID = 11598
    OR USERID = 11599
    OR USERID = 11607
    OR USERID = 11608
    OR USERID = 11609
    OR USERID = 11610
    OR USERID = 11618
    OR USERID = 11620
    OR USERID = 11629
    OR USERID = 11630
    OR USERID = 11631
    OR USERID = 11642
    OR USERID = 11650
    OR USERID = 11651
    OR USERID = 11652
    OR USERID = 12944
    OR ( USERID = 21760 AND ASSIGNCODEID = 963 )
    OR ASSIGNCODEID = 873
    OR ASSIGNCODEID = 871
    OR ASSIGNCODEID = 1178
    OR ( ASSIGNCODEID = 869 AND ASSIGNDATE BETWEEN '2013-03-01' AND '2013-03-31')
    ;
    So all records are deleted for all the userid's specified except:
    21760 where assigncodeid also must be 963

    And all records for all the assigncodeid's except the 869 where it must also be between the dates specified.

    Not quite what you had in mind right.

    Ref: http://dev.mysql.com/doc/refman/5.6/...recedence.html
    Last edited by sr; February 21st, 2013 at 03:30 PM.
    /Stefan
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,170
    Rep Power
    4274
    Originally Posted by tannercampbell
    And it's telling me to go diaf.
    dude, i never seen that error message before!!

    congratulations!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    8
    Rep Power
    0

    Thumbs up


    Originally Posted by r937
    dude, i never seen that error message before!!

    congratulations!!
    Custom NaviCat, only the worst MySQL guys have it.

    Thank for your help SR. I thought it was wrong because it wasn't returning any results ... turns out there was another value I needed to include (scheduleID) to get exactly what I was looking for. Thank you!

IMN logo majestic logo threadwatch logo seochat tools logo