5

Newbie MySql programmer thanks for the patience.

Im trying to track an Id number in a table where 3 different conditions are met this is what Iv got however the query dosent return any results where there are clearly matches in the table. Thoughts?

SELECT *
FROM `table`
WHERE `x` BETWEEN 80 AND 20
AND `y` BETWEEN 120 AND 20
AND `z` BETWEEN 40 AND 10
LIMIT 0 , 30

Am I right in theory to think that this should work?

Ken White
  • 117,855
  • 13
  • 197
  • 405
Christopher
  • 8,739
  • 8
  • 28
  • 37
  • It may not be strictly necessary, but please add parentheses to distinguish between the different ANDs and improve readability. – Thilo Mar 11 '11 at 02:39

3 Answers3

8

Close, but no cigar. :)

SELECT * FROM table 
WHERE (x BETWEEN 20 AND 80) AND 
(y BETWEEN 20 AND 120) AND 
(z BETWEEN 10 AND 40) LIMIT 0 , 30

To explain, SQL servers generally evaluate x BETWEEN val1 AND val2 the same as x >= val1 AND x <= val2. The way your original query was written, the first condition would be x >= 120 AND x <= 20), which obviously wasn't what you intended.

The parentheses around the different conditions make sure that each is evaluated completely before the AND is considered. It makes a difference most of the time in SQL, and even when it doesn't it's a good idea to use them so your intentions are clear 6 months from now when you (or someone else) has to look at the query again.

Ken White
  • 117,855
  • 13
  • 197
  • 405
2
SELECT * FROM table WHERE 
      (x BETWEEN 20 AND 80) AND 
      (y BETWEEN 20 AND 120) AND 
      (z BETWEEN 10 AND 40) 
LIMIT 0 , 30
rayman86
  • 1,335
  • 10
  • 9
1

I think the range needs to be the other way around:

SELECT * FROM table WHERE x BETWEEN 20 AND 80 AND y BETWEEN 20 AND 120 AND z BETWEEN 10 AND 40 LIMIT 0 , 30
Ewan Heming
  • 4,468
  • 1
  • 19
  • 20