homepage Welcome to WebmasterWorld Guest from 54.224.202.109
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Mysql Select
Not returning expected results
gosman




msg:4374080
 5:48 pm on Oct 13, 2011 (gmt 0)

I'm running the following statement

select minx,maxx,miny,maxy from TABLE where 523892 between minx AND maxx AND 156758 between miny AND maxy

There are 2 rows being returned. (below)

minx | maxx | miny | maxy


522962 | 524063 | 156729 | 157043
523867 | 523892 | 156742 | 156797



I can't understand why row 1 is being returned.

156758 does not fall between 156729 and 157043

It's driving me crazy, help much appreciated.

 

rocknbil




msg:4374094
 6:32 pm on Oct 13, 2011 (gmt 0)

Fascinating . . . .yes I'm a nerd . . .

create table minmax (id int(11) primary key auto_increment, minx int(11),maxx int(11), miny int(11), maxy int(11));

insert into minmax (minx,maxx,miny,maxy) values (522962,524063,156729,157043);
insert into minmax (minx,maxx,miny,maxy) values (523867,523892,156742,156797);

select minx,maxx,miny,maxy from minmax where 523892 between minx AND maxx AND 156758 between miny AND maxy;

FAIL!

select minx,maxx,miny,maxy from minmax where (523892 between minx AND maxx) AND (156758 between miny AND maxy);

FAIL! (and knew it would, but hadda try)

select minx,maxx,miny,maxy from minmax where 523892 > minx AND 523892 < maxx AND 156758 > miny AND 156758 < maxy;

WORKS!

I've no idea why between fails, the docs say it should be synonymous with the last statement. It must be that it's converting the data type or something, though explain doesn't tell us much.

gosman




msg:4374095
 6:42 pm on Oct 13, 2011 (gmt 0)

Hi rocknbil

Your solution doesn't work either. It returns

522962 | 524063 | 156729 | 157043

The result I'm looking for is

523867 | 523892 | 156742 | 156797

Regards

gosman

gosman




msg:4374096
 6:46 pm on Oct 13, 2011 (gmt 0)

Also rocknbil

if the value matches the min or max they are valid so I added the = sign to your statement and it produces the exact same result as my original statement

select minx,maxx,miny,maxy from ap_roads where 523892 >= minx AND 523892 <= maxx AND 156758 >= miny AND 156758 <= maxy;

It's driving me nuts now :(

httpwebwitch




msg:4374098
 6:56 pm on Oct 13, 2011 (gmt 0)

I added a row with 1,3,1,3 to illustrate

select minx,maxx,miny,maxy,(523892 between minx AND maxx) as x, (156758 between miny AND maxy) as y from minmax;


minx |maxx |miny |maxy |x|y
-----------------------------------------
1 |3 |1 |3 |0|0
522962|524063|156729|157043|1|1
523867|523892|156742|156797|1|1

httpwebwitch




msg:4374100
 7:01 pm on Oct 13, 2011 (gmt 0)

156758 does not fall between 156729 and 157043


yes it does

gosman




msg:4374102
 7:02 pm on Oct 13, 2011 (gmt 0)

Sorry httpwebwitch

I don't understand!

gosman




msg:4374103
 7:04 pm on Oct 13, 2011 (gmt 0)

httpwebwitch

My bad. I need some sleep.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved