Re: Between Operator

[mos]

At 05:51 PM 7/9/2004, you wrote:
Thanks everyone for helping out.    I took Michael's advice and made a 
new  table called ranking and two columns. It defiantly cleared some 
things up  but I am still having issues using the BETWEEN operator.  I 
just need to pull up everything BETWEEEN 10 and 18 and it keeps adding 
additional rows. Suggestions?  What am I doing wrong?

Here is my query:
SELECT area, style, route, stars, date_climbed, ranking.* FROM routes, 
ranking WHERE ranking.id = ranking.rating BETWEEN ranking.id < '10' AND 
ranking.id = '18' AND routes.rating = ranking.rating AND area = 'Eldorado 
Canyon' AND style = 'Traditonal' GROUP BY route ORDER BY id DESC
Craig,
        Did you read the MySQL manual? 
http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html has examples 
that will show you how the Between operator works.

SELECT area, style, route, stars, date_climbed, ranking.* FROM routes, 
ranking WHERE ranking.id = ranking.rating BETWEEN ranking.id < '10' AND 
ranking.id = '18' AND routes.rating = ranking.rating AND area = 'Eldorado 
Canyon' AND style = 'Traditonal' GROUP BY route ORDER BY id DESC

Your Between clause is being evaluated incorrectly because the two operands 
are being equated to gibberish. Here's what I think it is doing. I added 
parenthesis to try and demonstrate what MySQL is interpreting it as:

SELECT area, style, route, stars, date_climbed, ranking.* FROM routes, 
ranking WHERE ranking.id = (ranking.rating BETWEEN (ranking.id < '10') AND 
(ranking.id = '18')) AND routes.rating = ranking.rating AND area = 
'Eldorado Canyon' AND style = 'Traditonal' GROUP BY route ORDER BY id DESC

Are you sure you need this:  ranking.id = ranking.rating  ???
Basically I think all you need is:    WHERE  ranking.rating BETWEEN  '10' 
AND '18'

So it would look like this:
SELECT area, style, route, stars, date_climbed, ranking.* FROM routes, ranking
WHERE  ranking.rating BETWEEN  '10' AND '18' AND
routes.rating = ranking.rating AND area = 'Eldorado Canyon' AND style = 
'Traditonal'
GROUP BY route ORDER BY id DESC;

You can of course put the SQL statement on more than one line so you can 
read it better. I'm assuming Ranking.Rating is a Char or VarChar otherwise 
remove the quotes around '10' and '18'.

Mike


On Jul 9, 2004, at 1:17 PM, Pete Harlan wrote:
On Fri, Jul 09, 2004 at 09:39:02AM -0500, Craig Hoffman wrote:
Style:  Traditional
Area:  Yosemite
Rating: From: 5.5 To: 5.10c
...
"SELECT * FROM routes, users WHERE area='$area' AND style='$style'
BETWEEN rating='[$rating1]' AND rating='[$rating2]' GROUP BY route
ORDER BY rating ASC ";
 For some reason which I am not seeing, this query is not doing what it
should be doing.  Does anyone have any suggestions?
For starters your between syntax isn't correct (but is parsable in
ways you didn't want).  You probably want:
        select  *
        from    routes, users
        where   area = '$area'          and
                        style = '$style'        and
                        rating between '$rating1' and '$rating2'
        group by route
        order by rating
As others have pointed out, your ratings aren't something MySQL will
know how to order.  That's a separate problem (and more difficult to
solve), but the between syntax is also one.
--Pete
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]