Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
sql oddity (but weird)
Author: Matthew Small
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126722
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
Author: Tony Weeg
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126716
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
Author: Andy Ewings
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126717
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
Author: Michael T. Tangorre
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126715
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 -----
Author: Ben Forta
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126714
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
Author: Ben Forta
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126713
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 -----
Author: Tony Weeg
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126712
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
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126711
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
Author: jon hall
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126708
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
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126707
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
Author: Nagy, Daniel J
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126704
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
Author: Robertson-Ravo, Neil (RX)
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126703
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
Author: Tony Weeg
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126701
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
Author: Tony Weeg
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126699
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
Author: Tony Weeg
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126700
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
Author: Robertson-Ravo, Neil (RX)
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126698
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
Author: Robertson-Ravo, Neil (RX)
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126697
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
Author: Michael T. Tangorre
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126696
Ben???
Where have you been!!!
----- Excess quoted text cut - see Original Post for more -----
Author: Ben Forta
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126695
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
Author: Tony Weeg
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126694
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
Author: Robertson-Ravo, Neil (RX)
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25202#126693
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
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
