Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
phpPete
Forum Commoner
Posts: 97 Joined: Sun Aug 18, 2002 4:40 pm
Location: New Jersey
Post
by phpPete » Fri Aug 23, 2002 1:16 pm
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
??
llimllib
Moderator
Posts: 466 Joined: Mon Jul 01, 2002 2:19 pm
Location: Baltimore, MD
Post
by llimllib » Fri Aug 23, 2002 1:21 pm
The query is inclusive - A and G will be included.
phpPete
Forum Commoner
Posts: 97 Joined: Sun Aug 18, 2002 4:40 pm
Location: New Jersey
Post
by phpPete » Fri Aug 23, 2002 1:23 pm
Muchas gracias
phpPete
Forum Commoner
Posts: 97 Joined: Sun Aug 18, 2002 4:40 pm
Location: New Jersey
Post
by phpPete » Fri Aug 23, 2002 1:43 pm
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.
llimllib
Moderator
Posts: 466 Joined: Mon Jul 01, 2002 2:19 pm
Location: Baltimore, MD
Post
by llimllib » Fri Aug 23, 2002 1:49 pm
In the 'letters' test case, that does not happen.
phpPete
Forum Commoner
Posts: 97 Joined: Sun Aug 18, 2002 4:40 pm
Location: New Jersey
Post
by phpPete » Wed Sep 04, 2002 9:33 am
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