sql oddity (but weird)

Its perfectly valid.   In T-SQL you can have a constant on either the left or the right.   in essence 383709 does = ID just as much as ID = 383709 select * from routes where 10509 = id why does this work? I always thought that it was supposed to read like... select * from routes where id = 10509 column, before the value? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337

well I know that :) cause it works here...its just way backward from everything I was taught...thats all...whats CORRECT? either, neither, both? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Sent: Tuesday, July 01, 2003 8:54 AM To: CF-Talk Subject: RE: sql oddity (but weird) Its perfectly valid.   In T-SQL you can have a constant on either the left or the right.   in essence 383709 does = ID just as much as ID = 383709 select * from routes where 10509 = id why does this work? I always thought that it was supposed to read like... select * from routes where id = 10509 column, before the value? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337

Actually, you can have: WHERE column = column WHERE constant = constant WHERE column = constant WHERE constant = column All are allowed. select * from routes where 10509 = id why does this work? I always thought that it was supposed to read like... select * from routes where id = 10509 column, before the value? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337

Ben??? Where have you been!!! ----- Excess quoted text cut - see Original Post for more -----

Actually, funny you should ask .... I tried to post a message to cf-talk last week and it bounced saying I was not subscribed. I don't remember unsubscribing, but honestly, I also can't remember when I last received messages. Sad, eh? Anyway, once I figured it out I resubscribed immediately. Ben??? Where have you been!!! ----- Excess quoted text cut - see Original Post for more -----

Same thing happened to me, but don't ask Michael D about it, he had to clean up a bunch of my accounts from the past 5 years....  Once that happened, I resubscribed with one name and email and and was good to go! Mike ----- Excess quoted text cut - see Original Post for more -----

select * from routes where 10509 = 10509 doesn't produce anything close to what im lookin for...that just returns all rows...? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 Actually, you can have: WHERE column = column WHERE constant = constant WHERE column = constant WHERE constant = column All are allowed. select * from routes where 10509 = id why does this work? I always thought that it was supposed to read like... select * from routes where id = 10509 column, before the value? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337

Of course it returns all rows. You are telling the database that for each row retrieved, check if 10509 is equal to 10509 (which it will be). select * from routes where 10509 = 10509 doesn't produce anything close to what im lookin for...that just returns all rows...? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 Actually, you can have: WHERE column = column WHERE constant = constant WHERE column = constant WHERE constant = column All are allowed. select * from routes where 10509 = id why does this work? I always thought that it was supposed to read like... select * from routes where id = 10509 column, before the value? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337

ahhh of course ;) duh. anyway, its just looked strange to me this morning when jaye, my head cfmxflashman here showed me some code he had worked on, and I was like WOAHHHH but it worked then we had this mind-bending morning that made last weekend look like a birthday party at mcDonalds...thats all :) have a good day all! tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 Of course it returns all rows. You are telling the database that for each row retrieved, check if 10509 is equal to 10509 (which it will be). select * from routes where 10509 = 10509 doesn't produce anything close to what im lookin for...that just returns all rows...? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 Actually, you can have: WHERE column = column WHERE constant = constant WHERE column = constant WHERE constant = column All are allowed. select * from routes where 10509 = id why does this work? I always thought that it was supposed to read like... select * from routes where id = 10509 column, before the value? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337

Which can be useful in certain situations, such as building dynamic queries. Matthew Small IT Director Showstopper American Dance Championships matt@showstopperonline.com 843-357-1847 Of course it returns all rows. You are telling the database that for each row retrieved, check if 10509 is equal to 10509 (which it will be). select * from routes where 10509 = 10509 doesn't produce anything close to what im lookin for...that just returns all rows...? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 Actually, you can have: WHERE column = column WHERE constant = constant WHERE column = constant WHERE constant = column All are allowed. select * from routes where 10509 = id why does this work? I always thought that it was supposed to read like... select * from routes where id = 10509 column, before the value? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337

both are "correct"   it just really what you are used to etc..  its not a case of whats allowed etc, its a case of what will produce the desired result. well I know that :) cause it works here...its just way backward from everything I was taught...thats all...whats CORRECT? either, neither, both? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Sent: Tuesday, July 01, 2003 8:54 AM To: CF-Talk Subject: RE: sql oddity (but weird) Its perfectly valid.   In T-SQL you can have a constant on either the left or the right.   in essence 383709 does = ID just as much as ID = 383709 select * from routes where 10509 = id why does this work? I always thought that it was supposed to read like... select * from routes where id = 10509 column, before the value? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337

well, then it should always produce the desired result...if all are correct...neither should matter...all should work? correct?  I wonder why it is, and why I havent seen the other way value = column in any books (especially the one I got at devcon 2001 sql in 10 minutes!!!)  just weird, I guess just a matter of semantics...heh, cool. thanks. tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Sent: Tuesday, July 01, 2003 9:06 AM To: CF-Talk Subject: RE: sql oddity (but weird) both are "correct"   it just really what you are used to etc..  its not a case of whats allowed etc, its a case of what will produce the desired result. well I know that :) cause it works here...its just way backward from everything I was taught...thats all...whats CORRECT? either, neither, both? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Sent: Tuesday, July 01, 2003 8:54 AM To: CF-Talk Subject: RE: sql oddity (but weird) Its perfectly valid.   In T-SQL you can have a constant on either the left or the right.   in essence 383709 does = ID just as much as ID = 383709 select * from routes where 10509 = id why does this work? I always thought that it was supposed to read like... select * from routes where id = 10509 column, before the value? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337

Tony Weeg wrote: > well, then it should always produce the desired result...if all are > correct...neither > should matter...all should work? correct? <quote> ©ISO/IEC ISO/IEC 9075-2:1999 (E) 8.2 <comparison predicate> Function Specify a comparison of two row values. Format <comparison predicate> ::=      <row value expression> <comp op> <row value expression> </quote> Both sides are obviously interchangeable. Jochem

Tuesday, July 1, 2003, 9:25:45 AM, you wrote: JvD> Tony Weeg wrote: >> well, then it should always produce the desired result...if all are >> correct...neither >> should matter...all should work? correct? JvD> <quote> JvD> ©ISO/IEC ISO/IEC 9075-2:1999 (E) JvD> 8.2 <comparison predicate> JvD> Function JvD> Specify a comparison of two row values. JvD> Format JvD> <comparison predicate> ::= JvD>      <row value expression> <comp op> <row value expression> JvD> </quote> JvD> Both sides are obviously interchangeable. JvD> Jochem Just curiosity on my part, but what does ::= translate to in english? Seen it before...just never understood it. jon

j > > Just curiosity on my part, but what does ::= translate to in english? > Seen it before...just never understood it. http://cui.unige.ch/db-research/Enseignement/analyseinfo/AboutBNF.html Jochem

always there for the spec.  jochem do you speak in spec. when dining with friends ;) tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 Tony Weeg wrote: > well, then it should always produce the desired result...if all are > correct...neither should matter...all should work? correct? <quote> ©ISO/IEC ISO/IEC 9075-2:1999 (E) 8.2 <comparison predicate> Function Specify a comparison of two row values. Format <comparison predicate> ::=      <row value expression> <comp op> <row value expression> </quote> Both sides are obviously interchangeable. Jochem

However, I do agree with you, its not what gets taught but I am sure in your code are snippets which are certainly not set in stone somewhere... well I know that :) cause it works here...its just way backward from everything I was taught...thats all...whats CORRECT? either, neither, both? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Sent: Tuesday, July 01, 2003 8:54 AM To: CF-Talk Subject: RE: sql oddity (but weird) Its perfectly valid.   In T-SQL you can have a constant on either the left or the right.   in essence 383709 does = ID just as much as ID = 383709 select * from routes where 10509 = id why does this work? I always thought that it was supposed to read like... select * from routes where id = 10509 column, before the value? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337

of course my stuff is by the book ;) always... tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Sent: Tuesday, July 01, 2003 9:11 AM To: CF-Talk Subject: RE: sql oddity (but weird) However, I do agree with you, its not what gets taught but I am sure in your code are snippets which are certainly not set in stone somewhere... well I know that :) cause it works here...its just way backward from everything I was taught...thats all...whats CORRECT? either, neither, both? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Sent: Tuesday, July 01, 2003 8:54 AM To: CF-Talk Subject: RE: sql oddity (but weird) Its perfectly valid.   In T-SQL you can have a constant on either the left or the right.   in essence 383709 does = ID just as much as ID = 383709 select * from routes where 10509 = id why does this work? I always thought that it was supposed to read like... select * from routes where id = 10509 column, before the value? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337

Yes, it will as the SQL you have just pasted simply reads.... SELECT ALL FROM ROUTES WHERE VALUE X = VALUE Y The engine is simply working logically and getting all rows where the X = Y (which it is) Again, this is expected behaivour.   Think of it this way...If this was a CFIF it would simply be <cfif 10509 EQ 10509>   SELECT * FROM ROUTES  (RETURN ALL ROWS) <cfelse>   DO NOTHING (RETURN NADA) </cfif> select * from routes where 10509 = 10509 doesn't produce anything close to what im lookin for...that just returns all rows...? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 Actually, you can have: WHERE column = column WHERE constant = constant WHERE column = constant WHERE constant = column All are allowed. select * from routes where 10509 = id why does this work? I always thought that it was supposed to read like... select * from routes where id = 10509 column, before the value? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337

like the objectivists say, A = A. all you're doing is giving it a true boolean. since 10509 DOES equal 10509, you're going to get * back. --d. select * from routes where 10509 = 10509 doesn't produce anything close to what im lookin for...that just returns all rows...? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 Actually, you can have: WHERE column = column WHERE constant = constant WHERE column = constant WHERE constant = column All are allowed. select * from routes where 10509 = id why does this work? I always thought that it was supposed to read like... select * from routes where id = 10509 column, before the value? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337

MAkes no difference which comes first value or column.  Simple case of Mathematical equality.  This is why for the old inner join syntax (=) it didn't matter which table you had on left or right of join statement.  In fact doesn't matter using new syntax either Of course it returns all rows. You are telling the database that for each row retrieved, check if 10509 is equal to 10509 (which it will be). select * from routes where 10509 = 10509 doesn't produce anything close to what im lookin for...that just returns all rows...? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 Actually, you can have: WHERE column = column WHERE constant = constant WHERE column = constant WHERE constant = column All are allowed. select * from routes where 10509 = id why does this work? I always thought that it was supposed to read like... select * from routes where id = 10509 column, before the value? tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337