Page 1 of 1

BETWEEN question

Posted: Fri Aug 23, 2002 1:16 pm
by phpPete
Example

Code: Select all

"SELECT item_name, rec_code FROM menu_item WHERE item_name BETWEEN 'A' AND 'G' ";

Am I mistaken, but does this query exclude A and G in the results?

And if so, how do I get 'A" values? Also should I overlap the subseguent groupings?

ie

BETWEEN A and G
BETWEEN G and P
BEWTEEN P and Z

??

Posted: Fri Aug 23, 2002 1:21 pm
by llimllib
The query is inclusive - A and G will be included.

Posted: Fri Aug 23, 2002 1:23 pm
by phpPete
Muchas gracias

Posted: Fri Aug 23, 2002 1:43 pm
by phpPete
Interesting behavior. In testing, one of the groupings had nothing to return, so it "appears" MySQL grabbed the next result, which was out of the grouping.

So I asked for everything between 'H' AND 'P' and there is nothing in the DB to satisfy that query, so MySQL returns the first possible value from the next grouping 'Q' AND 'Z' ( Rigatoni with Chicken and Sausage )

When I inserted a record to satisfy each of the groupings it works fine.

Posted: Fri Aug 23, 2002 1:49 pm
by llimllib
In the 'letters' test case, that does not happen.

Posted: Wed Sep 04, 2002 9:33 am
by phpPete
OK...the final solution is to dump BETWEEN and go this route:

item_name >= 'A' and item_name < 'H' ...
item_name >= 'H' and item_name < 'Q'...
item_name >= 'Q' and item_name < CHAR(ORD('Z')+1)

as recommended by Paul Dubois, author MySQL