BETWEEN question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
phpPete
Forum Commoner
Posts: 97
Joined: Sun Aug 18, 2002 4:40 pm
Location: New Jersey

BETWEEN question

Post 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

??
User avatar
llimllib
Moderator
Posts: 466
Joined: Mon Jul 01, 2002 2:19 pm
Location: Baltimore, MD

Post by llimllib »

The query is inclusive - A and G will be included.
User avatar
phpPete
Forum Commoner
Posts: 97
Joined: Sun Aug 18, 2002 4:40 pm
Location: New Jersey

Post by phpPete »

Muchas gracias
User avatar
phpPete
Forum Commoner
Posts: 97
Joined: Sun Aug 18, 2002 4:40 pm
Location: New Jersey

Post 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.
User avatar
llimllib
Moderator
Posts: 466
Joined: Mon Jul 01, 2002 2:19 pm
Location: Baltimore, MD

Post by llimllib »

In the 'letters' test case, that does not happen.
User avatar
phpPete
Forum Commoner
Posts: 97
Joined: Sun Aug 18, 2002 4:40 pm
Location: New Jersey

Post 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
Post Reply