Multiple OR using IN - Help

Dev Shed Forums Sponsor:

February 21st, 2013, 02:55 PM

tannercampbell

Registered User

Join Date: Jan 2013

Posts: 8

Time spent in forums: 1 h 48 m 14 sec
Reputation Power: 0

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

February 21st, 2013, 02:59 PM

tannercampbell

Registered User

Join Date: Jan 2013

Posts: 8

Time spent in forums: 1 h 48 m 14 sec
Reputation 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.

February 21st, 2013, 03:26 PM

Problem Solver

Join Date: Jan 2001

Location: Stockholm, Sweden

Posts: 4,430

Time spent in forums: 3 Weeks 4 Days 7 h 23 m 50 sec
Reputation Power: 532

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...precedence.html

__________________
/Stefan

Last edited by sr : February 21st, 2013 at 03:30 PM.

February 21st, 2013, 04:23 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,371

Time spent in forums: 3 Months 1 Week 2 Days 6 h 32 m 9 sec
Reputation Power: 4140

Quote:

Originally Posted by tannercampbell

And it's telling me to go diaf.

dude, i never seen that error message before!!

congratulations!!

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

February 21st, 2013, 04:31 PM

tannercampbell

Registered User

Join Date: Jan 2013

Posts: 8

Time spent in forums: 1 h 48 m 14 sec
Reputation Power: 0

Quote:

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!

Multiple OR using IN - Help

Developer Shed Advertisers and Affiliates