Re: SQL Help
Yep, EXISTS will virtually always be faster, usually MUCH faster, than a correlated subquery, because a subquery is evaluated for EVERY ROW processed by the outer query. On Wed, Aug 26, 2009 at 12:43 AM, Mark Henderson shadefro...@gmail.comwrote: Brian Kotek wrote: WHERE NOT EXISTS should also work. Yes it does, and I knew about that method when using NOT IN, as it was a simple change to my original working query. What I didn't know, but now do after some googling, is that NOT EXISTS means it uses an index in the subquery as opposed to a full table scan (which was my primary concern). Thanks Brian. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325745 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Mark, What DBMS are you using? If its SQL Server, I don't think what you want to do is possible other than how you have already done it. If you find another way to do it (with a join) I would also be interested to see that. Kevin Roche ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325659 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Kevin Roche wrote: Mark, What DBMS are you using? If its SQL Server, I don't think what you want to do is possible other than how you have already done it. If you find another way to do it (with a join) I would also be interested to see that. Kevin Roche Hi Kevin, Yes it's MS SQL. Are you sure I can't do a left join and use IS NULL for the record that isn't allowed to match? If someone can tell me that the subquery is only run once then I'm not too perturbed and will leave it as is. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325660 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Mark, You might be right but I never got that to work myself, in MSSQL. An experiment you might try is to do that subquery separately in a different CFQUERY then plug in the retrieved value. Occasionally I have found that knid of trick is quicker. Kevin On Tue, Aug 25, 2009 at 11:49 AM, Mark Henderson shadefro...@gmail.comwrote: Kevin Roche wrote: Mark, What DBMS are you using? If its SQL Server, I don't think what you want to do is possible other than how you have already done it. If you find another way to do it (with a join) I would also be interested to see that. Kevin Roche Hi Kevin, Yes it's MS SQL. Are you sure I can't do a left join and use IS NULL for the record that isn't allowed to match? If someone can tell me that the subquery is only run once then I'm not too perturbed and will leave it as is. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325662 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Help
Try using an OUTER JOIN and specify the criteria in the JOIN statement
...
FROM table1
LEFT OUTER JOIN table2
ON table1.col1 = table2.col1
AND {criteria goes here}
...
Thanks,
Mike
-Original Message-
From: Mark Henderson [mailto:shadefro...@gmail.com]
Sent: Tuesday, August 25, 2009 4:27 AM
To: cf-talk
Subject: SQL Help
Greetings from the chilly south,
I have this query and it returns the expected result set, but I can't
work out how to use a join instead of the NOT IN clause and I *know*
that is going to be more efficient. Basically, I want to exclude the
current special from the result set. Any ideas?
cfquery name=qGetRecentRecord datasource=#request.dsn#
SELECT
TOP 1
RV.ID AS RecID
,RV.Date_Viewed
,FS.ID
,FS.Category_ID
,FS.Title
,FS.Comment
,FS.Thumbnail
,FS.Photo
,FS.Price
,FS.Year
,FS.Date_Modified
,FS.Active
,C.Name As Category_Name
,C.Image_Path
,C.Image_Dir
FROM
tbl_ForSaleCategories C
INNER JOIN (
tbl_RecentlyViewed RV
INNER JOIN
tbl_ForSale FS
ON RV.ID = FS.ID
)
ON C.ID = FS.Category_ID
WHERE RV.ID NOT IN (
SELECT ID
FROM tbl_CoverSpecial
)
AND FS.Active = 1
ORDER BY Date_Viewed ASC;
/cfquery
All help appreciated. TIA
adieu
Mark
---*/
~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325673
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
Try this: ... FROM tbl_ForSaleCategories C INNER JOIN ( tbl_RecentlyViewed RV INNER JOIN tbl_ForSale FS ON RV.ID = FS.ID ) ON C.ID = FS.Category_ID LEFT JOIN tbl_CoverSpecial CS ON RV.ID = CS.ID WHERE CS.ID IS NULL AND FS.Active = 1 ORDER BY Date_Viewed ASC; Mark Henderson wrote: Greetings from the chilly south, I have this query and it returns the expected result set, but I can't work out how to use a join instead of the NOT IN clause and I *know* that is going to be more efficient. Basically, I want to exclude the current special from the result set. Any ideas? cfquery name=qGetRecentRecord datasource=#request.dsn# SELECT TOP 1 RV.ID AS RecID ,RV.Date_Viewed ,FS.ID ,FS.Category_ID ,FS.Title ,FS.Comment ,FS.Thumbnail ,FS.Photo ,FS.Price ,FS.Year ,FS.Date_Modified ,FS.Active ,C.Name As Category_Name ,C.Image_Path ,C.Image_Dir FROM tbl_ForSaleCategories C INNER JOIN ( tbl_RecentlyViewed RV INNER JOIN tbl_ForSale FS ON RV.ID = FS.ID ) ON C.ID = FS.Category_ID WHERE RV.ID NOT IN ( SELECT ID FROM tbl_CoverSpecial ) AND FS.Active = 1 ORDER BY Date_Viewed ASC; /cfquery All help appreciated. TIA adieu Mark ---*/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325678 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Thanks Billy and Michael (and Kevin). After some trial and error I managed to stumble upon solution (the outer join gave me results but not the expected set). cfquery name=qGetRecentRecord datasource=#request.dsn# SELECT TOP 1 RV.ID AS Rec_ID ,RV.Date_Viewed ,FS.ID ,FS.Category_ID ,FS.Title ,FS.Comment ,FS.Thumbnail ,FS.Photo ,FS.Price ,FS.Year ,FS.Date_Modified ,C.Name As Category_Name ,C.Image_Path ,C.Image_Dir FROM tbl_RecentlyViewed RV INNER JOIN ( tbl_ForSaleCategories C INNER JOIN ( tbl_ForSale FS LEFT JOIN tbl_CoverSpecial CS ON FS.ID = CS.ID ) ON C.ID = FS.Category_ID ) ON RV.ID = FS.ID WHERE FS.Active = 1 AND CS.ID IS NULL /cfquery It can probably be improved but it works so I'm satisfied :-) adieu Mark ---*/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325705 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
WHERE NOT EXISTS should also work. On Tue, Aug 25, 2009 at 5:27 AM, Mark Henderson shadefro...@gmail.comwrote: Greetings from the chilly south, I have this query and it returns the expected result set, but I can't work out how to use a join instead of the NOT IN clause and I *know* that is going to be more efficient. Basically, I want to exclude the current special from the result set. Any ideas? cfquery name=qGetRecentRecord datasource=#request.dsn# SELECT TOP 1 RV.ID AS RecID ,RV.Date_Viewed ,FS.ID ,FS.Category_ID ,FS.Title ,FS.Comment ,FS.Thumbnail ,FS.Photo ,FS.Price ,FS.Year ,FS.Date_Modified ,FS.Active ,C.Name As Category_Name ,C.Image_Path ,C.Image_Dir FROM tbl_ForSaleCategories C INNER JOIN ( tbl_RecentlyViewed RV INNER JOIN tbl_ForSale FS ON RV.ID = FS.ID ) ON C.ID = FS.Category_ID WHERE RV.ID NOT IN ( SELECT ID FROM tbl_CoverSpecial ) AND FS.Active = 1 ORDER BY Date_Viewed ASC; /cfquery All help appreciated. TIA adieu Mark ---*/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325706 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Brian Kotek wrote: WHERE NOT EXISTS should also work. Yes it does, and I knew about that method when using NOT IN, as it was a simple change to my original working query. What I didn't know, but now do after some googling, is that NOT EXISTS means it uses an index in the subquery as opposed to a full table scan (which was my primary concern). Thanks Brian. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325707 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Basically, the cfset sqlToRun = ... / is kind of redundant and negates the benefit of the cfquery tag. Put all you SQL inside the cfquery tag. cfqueryparam is only valid within cfquery tags. Dominic 2009/6/28 Jason Slack applesl...@gmail.com: CF 8.01 OS X. I have: cfset sqlToRun = INSERT INTO personalevent(eventid, userid, username, eventdate) VALUES(#FORM.eventidentity#, #FORM.whois#, '#FORM.juser#', '#FORM.eventdate#') cfoutput #sqlToRun# /cfoutput cfquery name=addpersonaleventtome dataSource=cf_WikiData #sqlToRun# /cfquery the cfoutput is: INSERT INTO personalevent(eventid, userid, username, eventdate) VALUES(1, 1, 'jason', '2009-06-27') Which runs fine in a SQL Editor, but running that in the above cfquery I get: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1'', ''1'', ''jason'', ''2009-06-27'')' at line 1 There are a number of extra single quotes that I dont see why. If I try converting to crqueryparam I have something messed up because I get an error: I did: cfset sqlToRun = INSERT INTO personalevent(eventid, userid, username, eventdate) VALUES(cfqueryparam value=#FORM.eventidentity# cfsqltype=CF_SQL_INTEGER, cfqueryparam value=#FORM.whois# cfsqltype=CF_SQL_INTEGER, cfqueryparam value=#FORM.juser# cfsqltype=CF_SQL_VARCHAR, cfqueryparam value=#FORM.eventdate# cfsqltype=CF_SQL_DATE) and I get an error: Invalid CFML construct found on line 22 at column 120. Does anyone see my issue? -Jason ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324056 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Help
As Dominic said, putting the entire sql statement in as a variable in ColdFusion isn't necessary. Many languages like php that sort of thing is necessary but in ColdFusion, as Dominic said, it's just redundant. However, just for future knowledge sake, the reason your receiving this error is because when you feed in a variable with single quotes, for some reason they become duplicated when you feed it in sql. I've actually never seen this before mostly because it's not good practice in ColdFusion to do this. However, Just FYI though... the below query should work. cfset sqlToRun = INSERT INTO personalevent(eventid, userid, username, eventdate) VALUES(#FORM.eventidentity#, #FORM.whois#, '#FORM.juser#', '#FORM.eventdate#') cfquery name=addpersonaleventtome dataSource=cf_WikiData #replace(sqlToRun,'',',ALL)# /cfquery Paul Alkema -Original Message- From: Dominic Watson [mailto:watson.domi...@googlemail.com] Sent: Tuesday, June 30, 2009 5:22 AM To: cf-talk Subject: Re: SQL Help Basically, the cfset sqlToRun = ... / is kind of redundant and negates the benefit of the cfquery tag. Put all you SQL inside the cfquery tag. cfqueryparam is only valid within cfquery tags. Dominic 2009/6/28 Jason Slack applesl...@gmail.com: CF 8.01 OS X. I have: cfset sqlToRun = INSERT INTO personalevent(eventid, userid, username, eventdate) VALUES(#FORM.eventidentity#, #FORM.whois#, '#FORM.juser#', '#FORM.eventdate#') cfoutput #sqlToRun# /cfoutput cfquery name=addpersonaleventtome dataSource=cf_WikiData #sqlToRun# /cfquery the cfoutput is: INSERT INTO personalevent(eventid, userid, username, eventdate) VALUES(1, 1, 'jason', '2009-06-27') Which runs fine in a SQL Editor, but running that in the above cfquery I get: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1'', ''1'', ''jason'', ''2009-06-27'')' at line 1 There are a number of extra single quotes that I dont see why. If I try converting to crqueryparam I have something messed up because I get an error: I did: cfset sqlToRun = INSERT INTO personalevent(eventid, userid, username, eventdate) VALUES(cfqueryparam value=#FORM.eventidentity# cfsqltype=CF_SQL_INTEGER, cfqueryparam value=#FORM.whois# cfsqltype=CF_SQL_INTEGER, cfqueryparam value=#FORM.juser# cfsqltype=CF_SQL_VARCHAR, cfqueryparam value=#FORM.eventdate# cfsqltype=CF_SQL_DATE) and I get an error: Invalid CFML construct found on line 22 at column 120. Does anyone see my issue? -Jason ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324062 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
it's not good practice in ColdFusion to do this.
I don't agree with that : sometimes you have to do multiple updates, inserts or
else at the same time, depending on your process and some conditions; instead
of doing multiple cfquery (and thus multiple db connections), it is sometimes
cool to be able to put all the statement(s) in a variable. You then can do one
cfquery for the all list of statements at once (juste separate the statements
with a ;, in the string variable that you create).
If you encounter the problem of the quotes (number of extra single quotes in
the statement generated upon your variable), just use the
PreserveSingleQuotes() function; basically, your query will look like :
('mystring' is the variable that contains your statements)
cfquery name=myName datasource=myDsn
#PreserveSingleQuotes(mystring)#
/cfquery
As Dominic said, putting the entire sql statement in as a variable in
ColdFusion isn't necessary. Many languages like php that sort of thing is
necessary but in ColdFusion, as Dominic said, it's just redundant.
However, just for future knowledge sake, the reason your receiving this
error is because when you feed in a variable with single quotes, for some
reason they become duplicated when you feed it in sql. I've actually never
seen this before mostly because it's not good practice in ColdFusion to do
this.
However, Just FYI though... the below query should work.
cfset sqlToRun = INSERT INTO personalevent(eventid, userid, username,
eventdate) VALUES(#FORM.eventidentity#, #FORM.whois#, '#FORM.juser#',
'#FORM.eventdate#')
cfquery name=addpersonaleventtome dataSource=cf_WikiData
#replace(sqlToRun,'',',ALL)#
/cfquery
Paul Alkema
Basically, the cfset sqlToRun = ... / is kind of redundant and
negates the benefit of the cfquery tag. Put all you SQL inside the
cfquery tag. cfqueryparam is only valid within cfquery tags.
Dominic
CF 8.01 OS X.
I have:
cfset sqlToRun = INSERT INTO personalevent(eventid, userid, username,
eventdate) VALUES(#FORM.eventidentity#, #FORM.whois#, '#FORM.juser#',
'#FORM.eventdate#')
1, 'jason', '2009-06-27')
Which runs fine in a SQL Editor, but running that in the above cfquery I
get:
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '1'', ''1'',
''jason'', ''2009-06-27'')' at line 1
There are a number of extra single quotes that I dont see why.
If I try converting to crqueryparam I have something messed up because I
get an error:
I did:
cfset sqlToRun = INSERT INTO personalevent(eventid, userid, username,
eventdate) VALUES(cfqueryparam value=#FORM.eventidentity#
cfsqltype=CF_SQL_INTEGER, cfqueryparam value=#FORM.whois#
cfsqltype=CF_SQL_INTEGER, cfqueryparam value=#FORM.juser#
cfsqltype=CF_SQL_VARCHAR, cfqueryparam value=#FORM.eventdate#
cfsqltype=CF_SQL_DATE)
~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324063
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
As Dominic said, putting the entire sql statement in as a variable in ColdFusion isn't necessary. Please, there IS a very good reason one would put an SQL statement in a variable: when using some tool to generate build queries for instance. I have many examples in my own CMS, like a report builder, search engine, etc. However, just for future knowledge sake, the reason your receiving this error is because when you feed in a variable with single quotes, for some reason they become duplicated when you feed it in sql. I've actually never seen this before mostly because it's not good practice in ColdFusion to do this. The some reason is simply that any single quote in values in the statement must be escaped, and CF will do it automatically for any variable inside a CFQUERY tag. If you supply your own SQL statement, you are responsible for escaping single quotes yourself, and you must prevent CF to do it, because CF is not able to distinguish quotes in the statement from quotes in values, this is the purpose of the pseudo function preventSingleQuotes(). ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324064 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
And with preserveSingleQuotes() you have to hope you're better at cleaning input than hackers are at writing SQL injection. And yes, we all know you're totally awesome at it; this response is for others who'd rather not make that bet. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/6/30 Claude Schneegans schneeg...@internetique.com: As Dominic said, putting the entire sql statement in as a variable in ColdFusion isn't necessary. Please, there IS a very good reason one would put an SQL statement in a variable: when using some tool to generate build queries for instance. I have many examples in my own CMS, like a report builder, search engine, etc. However, just for future knowledge sake, the reason your receiving this error is because when you feed in a variable with single quotes, for some reason they become duplicated when you feed it in sql. I've actually never seen this before mostly because it's not good practice in ColdFusion to do this. The some reason is simply that any single quote in values in the statement must be escaped, and CF will do it automatically for any variable inside a CFQUERY tag. If you supply your own SQL statement, you are responsible for escaping single quotes yourself, and you must prevent CF to do it, because CF is not able to distinguish quotes in the statement from quotes in values, this is the purpose of the pseudo function preventSingleQuotes(). ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324065 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Help
Hmm.. yeah I can see your point. There are instances where it would be ok to
feed in an entire sql statement through cf variables. I think my point was
just that this shouldn't be a regular practice as it's unnecessary. It also
makes editing the sql statement more difficult and it's more bulky and more
difficult to read.
Also, as Claude stated in his email there are instances where you could have
a custom tag or a component that is really scalable and you may need to feed
in sql statements through to it. I can see the benefit of that.
-Original Message-
From: Stephane Vantroyen [mailto:s...@emakina.com]
Sent: Tuesday, June 30, 2009 8:55 AM
To: cf-talk
Subject: Re: SQL Help
it's not good practice in ColdFusion to do this.
I don't agree with that : sometimes you have to do multiple updates, inserts
or else at the same time, depending on your process and some conditions;
instead of doing multiple cfquery (and thus multiple db connections), it
is sometimes cool to be able to put all the statement(s) in a variable. You
then can do one cfquery for the all list of statements at once (juste
separate the statements with a ;, in the string variable that you create).
If you encounter the problem of the quotes (number of extra single quotes in
the statement generated upon your variable), just use the
PreserveSingleQuotes() function; basically, your query will look like :
('mystring' is the variable that contains your statements)
cfquery name=myName datasource=myDsn
#PreserveSingleQuotes(mystring)#
/cfquery
As Dominic said, putting the entire sql statement in as a variable in
ColdFusion isn't necessary. Many languages like php that sort of thing is
necessary but in ColdFusion, as Dominic said, it's just redundant.
However, just for future knowledge sake, the reason your receiving this
error is because when you feed in a variable with single quotes, for some
reason they become duplicated when you feed it in sql. I've actually never
seen this before mostly because it's not good practice in ColdFusion to do
this.
However, Just FYI though... the below query should work.
cfset sqlToRun = INSERT INTO personalevent(eventid, userid, username,
eventdate) VALUES(#FORM.eventidentity#, #FORM.whois#, '#FORM.juser#',
'#FORM.eventdate#')
cfquery name=addpersonaleventtome dataSource=cf_WikiData
#replace(sqlToRun,'',',ALL)#
/cfquery
Paul Alkema
Basically, the cfset sqlToRun = ... / is kind of redundant and
negates the benefit of the cfquery tag. Put all you SQL inside the
cfquery tag. cfqueryparam is only valid within cfquery tags.
Dominic
CF 8.01 OS X.
I have:
cfset sqlToRun = INSERT INTO personalevent(eventid, userid, username,
eventdate) VALUES(#FORM.eventidentity#, #FORM.whois#, '#FORM.juser#',
'#FORM.eventdate#')
1, 'jason', '2009-06-27')
Which runs fine in a SQL Editor, but running that in the above cfquery
I
get:
You have an error in your SQL syntax; check the manual that corresponds
to
your MySQL server version for the right syntax to use near '1'', ''1'',
''jason'', ''2009-06-27'')' at line 1
There are a number of extra single quotes that I dont see why.
If I try converting to crqueryparam I have something messed up because
I
get an error:
I did:
cfset sqlToRun = INSERT INTO personalevent(eventid, userid, username,
eventdate) VALUES(cfqueryparam value=#FORM.eventidentity#
cfsqltype=CF_SQL_INTEGER, cfqueryparam value=#FORM.whois#
cfsqltype=CF_SQL_INTEGER, cfqueryparam value=#FORM.juser#
cfsqltype=CF_SQL_VARCHAR, cfqueryparam value=#FORM.eventdate#
cfsqltype=CF_SQL_DATE)
~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324066
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
And with preserveSingleQuotes() you have to hope you're better at cleaning input than hackers are at writing SQL injection. When I'm talking about a CMS, I'm talking about some tool some customers have paid for and that is only accessible by approved users with authentication. Now if they want to hack and sabotage their own application they have paid for, it's their problem, and if it ever happens, they will pay again to get their own mess cleaned up. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324067 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
There's no reason you need a variable to do multiple updates in a single query statement. You can still put the SQL inside the query tags and, as you say, separate the statements with a semi-colon. Scott On Tue, Jun 30, 2009 at 6:55 AM, Stephane Vantroyens...@emakina.com wrote: I don't agree with that : sometimes you have to do multiple updates, inserts or else at the same time, depending on your process and some conditions; instead of doing multiple cfquery (and thus multiple db connections), it is sometimes cool to be able to put all the statement(s) in a variable. You then can do one cfquery for the all list of statements at once (juste separate the statements with a ;, in the string variable that you create). -- - Scott Brady http://www.scottbrady.net/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324069 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Help
Yeah, I agree, and I think the fact that you couldn't sanitize your inputs with cfqueryparam is a big reason to not do that. If it were me, and I had either multiple queries that I wanted to be executed at the same time or I had a custom tag or component that I needed to feed in sql too, I would just use a stored proc and feed in the name. -Original Message- From: Scott Brady [mailto:dsbr...@gmail.com] Sent: Tuesday, June 30, 2009 9:52 AM To: cf-talk Subject: Re: SQL Help There's no reason you need a variable to do multiple updates in a single query statement. You can still put the SQL inside the query tags and, as you say, separate the statements with a semi-colon. Scott On Tue, Jun 30, 2009 at 6:55 AM, Stephane Vantroyens...@emakina.com wrote: I don't agree with that : sometimes you have to do multiple updates, inserts or else at the same time, depending on your process and some conditions; instead of doing multiple cfquery (and thus multiple db connections), it is sometimes cool to be able to put all the statement(s) in a variable. You then can do one cfquery for the all list of statements at once (juste separate the statements with a ;, in the string variable that you create). -- - Scott Brady http://www.scottbrady.net/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324071 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
When I'm talking about a CMS, I'm talking about some tool some customers have paid for and that is only accessible by approved users with authentication. Now if they want to hack and sabotage their own application they have paid for, it's their problem, and if it ever happens, they will pay again to get their own mess cleaned up. Internal security problems are far more common than external ones. Within a large organization, not all users may be trustworthy. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324072 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
Sure, as long as the CMS has no XSS attack points... mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/6/30 Claude Schneegans schneeg...@internetique.com: And with preserveSingleQuotes() you have to hope you're better at cleaning input than hackers are at writing SQL injection. When I'm talking about a CMS, I'm talking about some tool some customers have paid for and that is only accessible by approved users with authentication. Now if they want to hack and sabotage their own application they have paid for, it's their problem, and if it ever happens, they will pay again to get their own mess cleaned up ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324073 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Internal security problems are far more common than external ones. Within a large organization, not all users may be trustworthy. C'mon, if they are users and they have access to the system, if they go crazy, do they really need SQL injection to harm the system ? They can simply delete all what they have access to, they can replace content by porn, whatever. Will CFQURYPARAM protect your application against that ? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324074 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
With SQL injection they can delete what they don't have access to. With XSS they can do that while making it look like someone else did it. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/6/30 Claude Schneegans schneeg...@internetique.com: Internal security problems are far more common than external ones. Within a large organization, not all users may be trustworthy. C'mon, if they are users and they have access to the system, if they go crazy, do they really need SQL injection to harm the system ? They can simply delete all what they have access to, they can replace content by porn, whatever. Will CFQURYPARAM protect your application against that ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324075 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
With XSS they can do that while making it look like someone else did it. Probably, but my clients barely know the difference between a computer and a toaster, and I spend more of my time explaining them that in order to press Ctrl, they must find a key on their keyboard with the letters Ctrl on it, and not press C then t, then r then l... than fighting against malicious nerds ;-) Ah, oh yes, also when you see Press Ctrl and something, you must press the Ctrl key and the other key IN THE SAME TIME, otherwise it won't work. The best for that is to use TWO fingers, possibly from two different hands... ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324076 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Help
Maybe that's what your customers are like, but trust me there are many, many hackers out their just waiting to find a website to do xss attacks or sql injection attacks. Your customers may not be computer savvy, but try explaining to them that their database got deleted because you weren't taking the precautions to make sure their database was being protected. Also, you talk about this like your speaking of only internal applications or applications that could never go on a production environment. You sell these custom tags. http://customtags.internetique.com/ And from the looks of it your feeding in sql into the custom tags just as you mentioned earlier. http://customtags.internetique.com/mapData/canadaMaps.cfm?m=4; http://customtags.internetique.com/mapData/canadaMaps.cfm?m=4;--; - Broken Page I would recommend looking into this further because if you're not careful you're going to get a lawsuit. The company I work for's website has approximately 80 attempted/failed hack attempts a day. Both XSS and SQl injection, mostly sql injection. If you don't take security in mind when writing applications it's just a matter of time before something bad happens. -Original Message- From: Claude Schneegans [mailto:schneeg...@internetique.com] Sent: Tuesday, June 30, 2009 11:19 AM To: cf-talk Subject: Re: SQL Help With XSS they can do that while making it look like someone else did it. Probably, but my clients barely know the difference between a computer and a toaster, and I spend more of my time explaining them that in order to press Ctrl, they must find a key on their keyboard with the letters Ctrl on it, and not press C then t, then r then l... than fighting against malicious nerds ;-) Ah, oh yes, also when you see Press Ctrl and something, you must press the Ctrl key and the other key IN THE SAME TIME, otherwise it won't work. The best for that is to use TWO fingers, possibly from two different hands... ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324079 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Help
If you don't take security in mind when writing applications it's just a matter of time before something bad happens. I can't tell you how many times I've been contacted by people who have had their site broken and need an emergency fix. I've made quite a bit of money fixing other people's broken code. One way or another, your client is going to pay for this basic level of security. Either they pay you to do it right when the code is written, or they pay a specialist like me to come in and repair the damage later (plus the cost of lost business, lost reputation, etc. resulting from the hack). The former is far less expensive than the latter. -Justin Scott ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324080 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
Also, you talk about this like your speaking of only internal applications or applications that could never go on a production environment. Indeed, we were talking about building SQL queries in a variable inside a Content management system. Of course, for parts of the site exposed to public, things are different. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324081 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
C'mon, if they are users and they have access to the system, if they go crazy, do they really need SQL injection to harm the system ? They can simply delete all what they have access to, they can replace content by porn, whatever. Will CFQURYPARAM protect your application against that ? Presumably, they have limited access to what they're allowed to change, and nothing else. User security is all about two things - authenticating users and limiting their access to specific items. You're acting as if the second concern doesn't exist. It does, within any sufficiently large environment. This is why enterprises don't let their users log in as local admins, why they buy content filters for outbound messaging, etc, etc, etc. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324083 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
if you're going to be generating your SQL like that, you'll need to wrap your final variable in preserveSingleQuotes(). so... cfquery name=addpersonaleventtome datasource=cf_WikiData #preserveSingleQuotes(sqlToRun)# /cfquery it will be pointed out to you (possibly before I even finish composing this response), that you are leaving yourself open to SQL injection attacks by not using cfqueryparam. Hopefully you're at least taking other steps to sanitize the inputs :) On Sun, Jun 28, 2009 at 11:23 AM, Jason Slack applesl...@gmail.com wrote: CF 8.01 OS X. I have: cfset sqlToRun = INSERT INTO personalevent(eventid, userid, username, eventdate) VALUES(#FORM.eventidentity#, #FORM.whois#, '#FORM.juser#', '#FORM.eventdate#') cfoutput #sqlToRun# /cfoutput cfquery name=addpersonaleventtome dataSource=cf_WikiData #sqlToRun# /cfquery the cfoutput is: INSERT INTO personalevent(eventid, userid, username, eventdate) VALUES(1, 1, 'jason', '2009-06-27') Which runs fine in a SQL Editor, but running that in the above cfquery I get: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1'', ''1'', ''jason'', ''2009-06-27'')' at line 1 There are a number of extra single quotes that I dont see why. If I try converting to crqueryparam I have something messed up because I get an error: I did: cfset sqlToRun = INSERT INTO personalevent(eventid, userid, username, eventdate) VALUES(cfqueryparam value=#FORM.eventidentity# cfsqltype=CF_SQL_INTEGER, cfqueryparam value=#FORM.whois# cfsqltype=CF_SQL_INTEGER, cfqueryparam value=#FORM.juser# cfsqltype=CF_SQL_VARCHAR, cfqueryparam value=#FORM.eventdate# cfsqltype=CF_SQL_DATE) and I get an error: Invalid CFML construct found on line 22 at column 120. Does anyone see my issue? -Jason ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324027 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Right I am switching everything to cfqueryparam as I read about SQL injection. Do you see my Invalid CFML construct found on line 22 at column 120. above though? I still dont. -Jason if you're going to be generating your SQL like that, you'll need to wrap your final variable in preserveSingleQuotes(). so... cfquery name=addpersonaleventtome datasource=cf_WikiData #preserveSingleQuotes(sqlToRun)# /cfquery it will be pointed out to you (possibly before I even finish composing this response), that you are leaving yourself open to SQL injection attacks by not using cfqueryparam. Hopefully you're at least taking other steps to sanitize the inputs :) On Sun, Jun 28, 2009 at 11:23 AM, Jas ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324029 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
A) Always use cfqueryparam/. (Note the period.) B) When in doubt, use cfqueryparam/ anyways. (Note the period.) C) While preserveSingleQuotes() can be a useful tool at times, I would have a very difficult time thinking of a time where I would use it. D) Always use cfqueryparam/. (Note again, the period.) E) You cannot use cfqueryparam/ in the midst of a cfset /. F) Always use cfqueryparam/. (Once again, a period.) cfquery INSERT INTO personalevent ( eventid, userid, username, eventdate ) VALUES ( cfqueryparam value=#FORM.eventidentity# cfsqltype=CF_SQL_INTEGER, cfqueryparam value=#FORM.whois# cfsqltype=CF_SQL_INTEGER, cfqueryparam value=#FORM.juser# cfsqltype=CF_SQL_VARCHAR, cfqueryparam value=#FORM.eventdate# cfsqltype=CF_SQL_DATE ); /cfquery G) In case you missed it, ALWAYS use cfqueryparam/. PERIOD. :-) HTH ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324030 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL help..
Just answered this on the SQL list: http://www.houseoffusion.com/groups/sql/thread.cfm/threadid:855 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314541 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL help..
I'm really greatful to you for the post. Just answered this on the SQL list: http://www.houseoffusion.com/groups/sql/thread.cfm/threadid:855 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314544 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
Assuming your PK is named customerID you can do the following: SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state FROM mytable WHERE mytable.customerID in (select min(customerID) from mytable group by email) There is at least one problem in your query. The in () statement is missing a From statement so that it should read: SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state FROM mytable WHERE mytable.email in (select distinct mytable.email from mytable) But this will not work since all records (even the doublettes) match the criteria. Gert Jeff F schrieb: I've got a table (MySQL) with about 20k records. I'd like to be able to get all fields from the table with distinct email addresses. Essentially, I'm weeding out records with duplicate email addresses. What I'm trying does not work: SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state FROM mytable WHERE mytable.email in (select distinct mytable.email) What am I missing?? -Johny B ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312575 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Help
To view all your records with duplicate email addresses, you might try something like this: cfquery name=select_distinct_email datasource=dsn select distinct email from mytable /cfquery cfloop query=select_distinct_email cfquery name=get_records_with_duplicate_email datasource=dsn select lastname, firstname, city, state from mytable where email = '#select_distinct_email.email#' /cfquery cfoutput query=get_offices_with_duplicate_cities #city_state# #office_name#br br /cfoutput /cfloop Check that output and see if that's what you're looking for... Rick -Original Message- From: Jeff F [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 16, 2008 8:58 AM To: CF-Talk Subject: SQL Help I've got a table (MySQL) with about 20k records. I'd like to be able to get all fields from the table with distinct email addresses. Essentially, I'm weeding out records with duplicate email addresses. What I'm trying does not work: SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state FROM mytable WHERE mytable.email in (select distinct mytable.email) What am I missing?? -Johny B ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312576 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
On Tue, Sep 16, 2008 at 8:58 AM, Jeff F [EMAIL PROTECTED] wrote: I've got a table (MySQL) with about 20k records. I'd like to be able to get all fields from the table with distinct email addresses. Essentially, I'm weeding out records with duplicate email addresses. What I'm trying does not work: SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state FROM mytable WHERE mytable.email in (select distinct mytable.email) What you are doing there will return every record in the table. If you really want to eliminate any records where there are duplicate emails, you could do this: SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state, mytable.email FROM mytable GROUP BY mytable.email HAVING count(mytable.email) = 1 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312579 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Help
Sorry about the output part in the bottom; that was from my test query. Should be: cfquery name=select_distinct_email datasource=dsn select distinct email from mytable /cfquery cfloop query=select_distinct_email cfquery name=get_records_with_duplicate_email datasource=dsn select lastname, firstname, city, state from mytable where email = '#select_distinct_email.email#' /cfquery cfoutput query=get_records_with_duplicate_email #lastname# #firstname# #city# #state#br br /cfoutput /cfloop -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 16, 2008 9:42 AM To: CF-Talk Subject: RE: SQL Help To view all your records with duplicate email addresses, you might try something like this: cfquery name=select_distinct_email datasource=dsn select distinct email from mytable /cfquery cfloop query=select_distinct_email cfquery name=get_records_with_duplicate_email datasource=dsn select lastname, firstname, city, state from mytable where email = '#select_distinct_email.email#' /cfquery cfoutput query=get_offices_with_duplicate_cities #city_state# #office_name#br br /cfoutput /cfloop Check that output and see if that's what you're looking for... Rick -Original Message- From: Jeff F [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 16, 2008 8:58 AM To: CF-Talk Subject: SQL Help I've got a table (MySQL) with about 20k records. I'd like to be able to get all fields from the table with distinct email addresses. Essentially, I'm weeding out records with duplicate email addresses. What I'm trying does not work: SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state FROM mytable WHERE mytable.email in (select distinct mytable.email) What am I missing?? -Johny B ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312577 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Jim, At first glance that seems to work, however the recordcounts appear to be off. What I did was a simple query to find the total number of distinct email addresses: SELECT distinct mytable.email FROM mytable I get 19588 as a recordcount. When I run SELECT mytable.lastname, mytable.email FROM mytable GROUP BY mytable.email HAVING count(mytable.email) = 1 I get 19162 as a recordcount. ?? I don't understand where the difference is? -John ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312585 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
On Tue, Sep 16, 2008 at 10:13 AM, Jeff F [EMAIL PROTECTED] wrote: SELECT distinct mytable.email FROM mytable I get 19588 as a recordcount. This number would include email addresses that are duplicated in the table (but only a count of 1 for each distinct address). SELECT mytable.lastname, mytable.email FROM mytable GROUP BY mytable.email HAVING count(mytable.email) = 1 I get 19162 as a recordcount. ?? This recordcount eliminates any email addresses that are duplicated in the table. Note that any email addresses that are duplicated are NOT INCLUDED in the results using this method. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312587 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
Thanks Jim. Now I see. I guess what I'm looking for would be something like this then: SELECT mytable.lastname,mytable.email FROM mytable GROUP BY mytable.email HAVING distinct(mytable.email) Which of course does not work. When there are records with duplicate emails addresses, I need to include one of them. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312588 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Are the other fields in your table the same when the email is the same? Meaning, are the records really duplicate? Or is it just the email that is duplicate and the other fields may have varying values for two rows that have the same email? If they do vary, do you care which of the duplicate rows you return? Judah On Tue, Sep 16, 2008 at 7:50 AM, Jeff F [EMAIL PROTECTED] wrote: Thanks Jim. Now I see. I guess what I'm looking for would be something like this then: SELECT mytable.lastname,mytable.email FROM mytable GROUP BY mytable.email HAVING distinct(mytable.email) Which of course does not work. When there are records with duplicate emails addresses, I need to include one of them. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312597 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
When there are records with duplicate emails addresses, I need to include one of them. So you are trying to display one record for each email address? You could try something like this. Not tested, but the idea is to select a single PK for each email. Then use a JOIN to display the details for those PK's. SELECT m.lastname, m.firstname, m.city, m.state FROM mytable AS m INNER JOIN ( SELECT email, max(YourPK) AS YourPK FROMmyTable GROUP BY email ) as e ON m.YourPK = e.YourPK ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312598 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
You could try something like this. Not tested, but the idea is to select a single PK for each email. Then use a JOIN to display the details for those PK's. Note, the previous query assumes it does not matter which record is returned. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312599 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
The records are from contest entries. People can only enter with one email address. Some people entered multiple times, using the same email address. I need to get a record set used to pick a winner, including just one of the records from the duplicate email entries. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312607 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
I need to get a record set used to pick a winner, including just one of the records from the duplicate email entries. If it does not matter which one, try the query I posted in my first response. The syntax is not tested, but it has the right concept. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312614 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
The records are from contest entries. People can only enter with one email address. Some people entered multiple times, using the same email address. I need to get a record set used to pick a winner, including just one of the records from the duplicate email entries. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312616 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
The records are from contest entries. People can only enter with one email address. Some people entered multiple times, using the same email address. So, why all the complexity with joins and subqueries? Just... SELECT DISTINCT email FROM sometable then pick a winner from the list of unique e-mail addresses. Go back and grab one of the entries with that e-mail address at random for the contact information. This way you're picking from a list of unique e-mail addresses that entered the drawing. It also begs the question, if they were only supposed to have one entry per e-mail address, why wasn't there error checking or a constraint on the table to force this in the first place? -Justin Scott, http://www.tlson.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312618 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
It also begs the question, if they were only supposed to have one entry per e-mail address, why wasn't there error checking or a constraint on the table to force this in the first place? True enough. I was thinking the same thing myself ;-) ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312620 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: sql help NEQ
ok i go it i think but it just looks so weird to me.. it looks back wards but works... SELECT DISTINCT TOP 100 PERCENT dbo.V_riprod_ZMATMAST.sap_partnum AS NEQnumb FROM dbo.V_riprod_ZMATMAST LEFT OUTER JOIN dbo.V_riprod_Specs_ZMATMAST_EQ ON dbo.V_riprod_ZMATMAST.sap_partnum = dbo.V_riprod_Specs_ZMATMAST_EQ.sap_partnum WHERE (dbo.V_riprod_Specs_ZMATMAST_EQ.sap_partnum IS NULL) ORDER BY dbo.V_riprod_ZMATMAST.sap_partnum ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302563 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql help!!!!!!!!!!
no dice cause it was 2am and didnt want to think about it no more Dunno if this is right, just woke up, but I'll take a stab at it. Looks like you need another join to the upsell table, then order by its sort by column first. SELECT brands.brand_id, brands.brand_name, brands.brand_logo, products.product_id, products.brand_id, products.product_item, products.category_id, products.product_image, products. product_archived, products.product_discontinued, upsell.upsell_id, upsell.product_id, upsell.upsell_product_id, upsell.upsell_sort FROM products, brands, upsell WHERE products.brand_id = brands.brand_id AND products.product_id = upsell.product_id AND products.product_id in (20,21) ORDER BY upsell.upsell_sort ASC, brands.brand_name ASC, products.product_item ASC And what's with the... sql help!! Sounds like you're goin' down on the titanic! :) Will ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293726 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql help!!!!!!!!!!
any good suggestions? Dunno if this is right, just woke up, but I'll take a stab at it. Looks like you need another join to the upsell table, then order by its sort by column first. SELECT brands.brand_id, brands.brand_name, brands.brand_logo, products.product_id, products.brand_id, products.product_item, products.category_id, products.product_image, products. product_archived, products.product_discontinued, upsell.upsell_id, upsell.product_id, upsell.upsell_product_id, upsell.upsell_sort FROM products, brands, upsell WHERE products.brand_id = brands.brand_id AND products.product_id = upsell.product_id AND products.product_id in (20,21) ORDER BY upsell.upsell_sort ASC, brands.brand_name ASC, products.product_item ASC And what's with the... sql help!! Sounds like you're goin' down on the titanic! :) Will ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293711 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Help, please ...
Let me get this straight, you want a report to summarize the number of installs by date and client. So client 1 ran 3 installs on 6/1 and 2 installs on 6/2. I believe you simply need to group by hostname, and then date and then use an aggregate function (count()) to add up the records in between like so: SELECT hostname, iDate, count(1) FROM installs GROUP BY hostname, iDate ORDER BY hostname, iDate The order by is optional, but I threw it in since your result set was ordered that way. Depending on whether or not your iDate column stores time or not, you may need to convert it to date only. (syntax depends on your DB). ~Brad -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 10:16 PM To: CF-Talk Subject: SQL Help, please ... Hello, It's been a wile since I have had to do much in the way of SQL queries and I need some help. I have a table called installs that looks like this HostName, iDate, Package client1, 2007-06-01, Update1 client1, 2007-06-01, Update2 client1, 2007-06-01, Update3 client1, 2007-06-02, Update5 client1, 2007-06-02, NewApp client2, 2007-06-01, Update1 client2, 2007-06-01, Update2 client2, 2007-06-01, Update3 client2, 2007-06-02, Update5 client2, 2007-06-02, NewApp What I want is a report/summary like result that looks like this HostName, iDate, Installs client1, 2007-06-01, 3 client1, 2007-06-02, 2 client2, 2007-06-01, 3 client2, 2007-06-02, 2 I hope this makes sense and I know this is off topic but any hep would be great. Thanks, tom ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282367 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help, please ...
[EMAIL PROTECTED] wrote: HostName, iDate, Package client1, 2007-06-01, Update1 client1, 2007-06-01, Update2 client1, 2007-06-01, Update3 client1, 2007-06-02, Update5 client1, 2007-06-02, NewApp client2, 2007-06-01, Update1 client2, 2007-06-01, Update2 client2, 2007-06-01, Update3 client2, 2007-06-02, Update5 client2, 2007-06-02, NewApp What I want is a report/summary like result that looks like this HostName, iDate, Installs client1, 2007-06-01, 3 client1, 2007-06-02, 2 client2, 2007-06-01, 3 client2, 2007-06-02, 2 SELECT HostName, iDate, COUNT(Package) FROM installs GROUP BY HostName, iDate ORDER BY HostName, iDate Jochem ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282372 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help - Answered
removing the DISTINCT did the trick. Thank you both. Josh Nathanson wrote: I don't think you need DISTINCT at all if you are using GROUP BY. GROUP BY will group all the matching rate values together automatically, so the result will be all distinct values. Try something like (not tested) SELECT Count(rate) as ratecount, rate FROM myrates etc. GROUP BY rate Dump that and see if you're any closer to what you want. -- Josh - Original Message - From: [EMAIL PROTECTED] [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Tuesday, April 03, 2007 2:09 PM Subject: Re: SQL Help - Answered I'm going to have to look at something. I'm still not getting what I anticipated. There are over 3700 records of which 1775 of them are distinct values for RATE. However, I am only getting values of 1 for COUNT(DISTINCT rate) as rateCount. I was trying to find out how many records are there for each distinct value in the RATE field. I need to chart how many records there are for each value in RATE. Thanks for the help. Greg Morphis wrote: No problem, if you want to know why take a look at aggregate functions, which is what count is, as well as others.. On 4/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Thank you Greg Morphis wrote: You need a group by in your query.. SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 GROUP BY rate ORDER BY rate On 4/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Why do the first two queries work and the last one fail? The only difference is adding the comma and additional field to the SELECT statement. cfquery datasource=myDSN name=Rates SELECT count(DISTINCT rate) as rateCount FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery cfquery datasource=myDSN name=Rates SELECT rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery FAIL cfquery datasource=myDSN name=Rates SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274507 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
You need a group by in your query.. SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 GROUP BY rate ORDER BY rate On 4/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Why do the first two queries work and the last one fail? The only difference is adding the comma and additional field to the SELECT statement. cfquery datasource=myDSN name=Rates SELECT count(DISTINCT rate) as rateCount FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery cfquery datasource=myDSN name=Rates SELECT rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery FAIL cfquery datasource=myDSN name=Rates SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274477 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help - Answered
Thank you Greg Morphis wrote: You need a group by in your query.. SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 GROUP BY rate ORDER BY rate On 4/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Why do the first two queries work and the last one fail? The only difference is adding the comma and additional field to the SELECT statement. cfquery datasource=myDSN name=Rates SELECT count(DISTINCT rate) as rateCount FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery cfquery datasource=myDSN name=Rates SELECT rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery FAIL cfquery datasource=myDSN name=Rates SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274479 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help - Answered
No problem, if you want to know why take a look at aggregate functions, which is what count is, as well as others.. On 4/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Thank you Greg Morphis wrote: You need a group by in your query.. SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 GROUP BY rate ORDER BY rate On 4/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Why do the first two queries work and the last one fail? The only difference is adding the comma and additional field to the SELECT statement. cfquery datasource=myDSN name=Rates SELECT count(DISTINCT rate) as rateCount FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery cfquery datasource=myDSN name=Rates SELECT rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery FAIL cfquery datasource=myDSN name=Rates SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274480 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help - Answered
I'm going to have to look at something. I'm still not getting what I anticipated. There are over 3700 records of which 1775 of them are distinct values for RATE. However, I am only getting values of 1 for COUNT(DISTINCT rate) as rateCount. I was trying to find out how many records are there for each distinct value in the RATE field. I need to chart how many records there are for each value in RATE. Thanks for the help. Greg Morphis wrote: No problem, if you want to know why take a look at aggregate functions, which is what count is, as well as others.. On 4/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Thank you Greg Morphis wrote: You need a group by in your query.. SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 GROUP BY rate ORDER BY rate On 4/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Why do the first two queries work and the last one fail? The only difference is adding the comma and additional field to the SELECT statement. cfquery datasource=myDSN name=Rates SELECT count(DISTINCT rate) as rateCount FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery cfquery datasource=myDSN name=Rates SELECT rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery FAIL cfquery datasource=myDSN name=Rates SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274481 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help - Answered
can you provide a sample of your data and the way your table is designed (column name, type)? Thanks On 4/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I'm going to have to look at something. I'm still not getting what I anticipated. There are over 3700 records of which 1775 of them are distinct values for RATE. However, I am only getting values of 1 for COUNT(DISTINCT rate) as rateCount. I was trying to find out how many records are there for each distinct value in the RATE field. I need to chart how many records there are for each value in RATE. Thanks for the help. Greg Morphis wrote: No problem, if you want to know why take a look at aggregate functions, which is what count is, as well as others.. On 4/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Thank you Greg Morphis wrote: You need a group by in your query.. SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 GROUP BY rate ORDER BY rate On 4/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Why do the first two queries work and the last one fail? The only difference is adding the comma and additional field to the SELECT statement. cfquery datasource=myDSN name=Rates SELECT count(DISTINCT rate) as rateCount FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery cfquery datasource=myDSN name=Rates SELECT rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery FAIL cfquery datasource=myDSN name=Rates SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274482 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help - Answered
I don't think you need DISTINCT at all if you are using GROUP BY. GROUP BY will group all the matching rate values together automatically, so the result will be all distinct values. Try something like (not tested) SELECT Count(rate) as ratecount, rate FROM myrates etc. GROUP BY rate Dump that and see if you're any closer to what you want. -- Josh - Original Message - From: [EMAIL PROTECTED] [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Tuesday, April 03, 2007 2:09 PM Subject: Re: SQL Help - Answered I'm going to have to look at something. I'm still not getting what I anticipated. There are over 3700 records of which 1775 of them are distinct values for RATE. However, I am only getting values of 1 for COUNT(DISTINCT rate) as rateCount. I was trying to find out how many records are there for each distinct value in the RATE field. I need to chart how many records there are for each value in RATE. Thanks for the help. Greg Morphis wrote: No problem, if you want to know why take a look at aggregate functions, which is what count is, as well as others.. On 4/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Thank you Greg Morphis wrote: You need a group by in your query.. SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 GROUP BY rate ORDER BY rate On 4/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Why do the first two queries work and the last one fail? The only difference is adding the comma and additional field to the SELECT statement. cfquery datasource=myDSN name=Rates SELECT count(DISTINCT rate) as rateCount FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery cfquery datasource=myDSN name=Rates SELECT rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery FAIL cfquery datasource=myDSN name=Rates SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate /cfquery ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274483 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL help
Rick Root wrote: I'm trying to figure out how to do something in SQL and I'm stumped. I solved this problem with a view, and it works great. Rick ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236815 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL help (updated)
That probably needs to be WHERE #idnumber# IN (MyColumn) Ian, Yes, I tried that. I had typoed my e-mail, but in my code I had the parenthesis. That's what give the invalid comparison error. It doesn't seem to recognize the field. Dave ** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215394 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL help (updated)
This isn't a table. It's a search results query, and there is no other way to provide the information since cfindex only allows two custom fields. I have to pass it back to my search results page as a list. Dave -Original Message- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 16, 2005 4:37 PM To: CF-Talk Subject: Re: SQL help (updated) You realize that this is essentially a db design issue - right? The table shouldn't be holding lists of numbers - there should be a join table that does that job. Right? On 8/16/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Okay, I still need help, but I've resolve part of my problem. I'm able now to have the list of ID numbers in one field by themselves. So, my new field value (MyColumn) looks like this: '5,2,3,4,45,7' ** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215395 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL help Query of Queries (SOLVED) was (SQL help)
Jochem, Your method worked, I just had to add commas to the LIKE expression: SELECT * FROM AllResults WHERE ',' MyColumn ',' LIKE '%,#idnumber#,%' Thanks very much! Dave -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 16, 2005 4:35 PM To: CF-Talk Subject: Re: SQL help (updated) [EMAIL PROTECTED] wrote: Okay, I still need help, but I've resolve part of my problem. I'm able now to have the list of ID numbers in one field by themselves. So, my new field value (MyColumn) looks like this: '5,2,3,4,45,7' I still need a way using Query of Queries to extract only the records that have the 'idnumber' in that list. I've tried: SELECT * FROM AllResults WHERE #idnumber# in MyColumn SELECT * FROM AllResults WHERE ',' MyColumn ',' LIKE '%#idnumber#%' Jochem ** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215399 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL help (updated)
Okay, I still need help, but I've resolve part of my problem. I'm able now to have the list of ID numbers in one field by themselves. So, my new field value (MyColumn) looks like this: '5,2,3,4,45,7' I still need a way using Query of Queries to extract only the records that have the 'idnumber' in that list. I've tried: SELECT * FROM AllResults WHERE #idnumber# in MyColumn I get an error: Query Of Queries syntax error. Encountered 3 in custom2 at line 0, column 0. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition TIA, Dave -Original Message- From: Phillips, Dave Sent: Tuesday, August 16, 2005 2:16 PM To: CF-Talk Subject: SQL help Hi, I'm on Oracle 9i. I have a query (call it AllResults) that is returning a column (call it MyColumn) that's value looks like this: 'identifier|5,2,3,4,45,7' Each row may have a different 'identifier' and a different quantity of numbers. Basically, there are two values in this column. identifier numberlist separated by a pipe Now, I need to build a query of queries that queries this existing recordset and only returns the records which has a number that I'm comparing against. I know I can do this in CF by looping over the recordset, but I'm trying to avoid that (for processing time sake). Here's the pseudo code of what I'm trying to do: (idnumber is a variable in my code and will be ONE of the numbers listed in the numberlist) select * from AllResults WHERE idnumber is found in the list of numbers located in the MyColumn field I only want the records that have 'idnumber' IN the list of numbers I'm sure this can be done with some SQL code and since I'm not a SQL guru, let alone Oracle, any help or suggestions would be appreciated. Thanks! Dave ** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215299 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL help (updated)
SELECT * FROM AllResults WHERE #idnumber# in MyColumn That probably needs to be WHERE #idnumber# IN (MyColumn) The values of an IN clause are supposed to be in parenthesis I believe. You may also need to do something about the quotes if they are part of the value list. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215305 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL help (updated)
[EMAIL PROTECTED] wrote: Okay, I still need help, but I've resolve part of my problem. I'm able now to have the list of ID numbers in one field by themselves. So, my new field value (MyColumn) looks like this: '5,2,3,4,45,7' I still need a way using Query of Queries to extract only the records that have the 'idnumber' in that list. I've tried: SELECT * FROM AllResults WHERE #idnumber# in MyColumn SELECT * FROM AllResults WHERE ',' MyColumn ',' LIKE '%#idnumber#%' Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215335 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL help (updated)
You realize that this is essentially a db design issue - right? The table shouldn't be holding lists of numbers - there should be a join table that does that job. Right? On 8/16/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Okay, I still need help, but I've resolve part of my problem. I'm able now to have the list of ID numbers in one field by themselves. So, my new field value (MyColumn) looks like this: '5,2,3,4,45,7' ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215336 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Help Please
and companyid in (select distinct companyid Matthew Small Web Developer American City Business Journals 704-973-1045 [EMAIL PROTECTED] -Original Message- From: Jeff Fongemie [mailto:[EMAIL PROTECTED] Sent: Friday, June 03, 2005 10:20 AM To: CF-Talk Subject: SQL Help Please I've got a query that I just can't get right. select * From companies where companyhide = 0 and companyid = (select distinct companyid From releases where date_entered = DATE_SUB(curdate (),INTERVAL 8 day) and date_entered = DATE_SUB (curdate(),INTERVAL 30 day) order by date_entered desc) order by rank, company Essentially, I've got a table of companies and anothr table with press releases. press releases are linked to companies by company id. What I'm trying to do is select only companies that have press releases within the last for weeks. What the above is giving me is a list of companuid's, whihc does not work. Any help or suggestion would be much appreciated. -jeff ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208505 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Help Please
I've got a query that I just can't get right. select * From companies where companyhide = 0 and companyid = (select distinct companyid From releases where date_entered = DATE_SUB(curdate (),INTERVAL 8 day) and date_entered = DATE_SUB (curdate(),INTERVAL 30 day) order by date_entered desc) order by rank, company Essentially, I've got a table of companies and anothr table with press releases. press releases are linked to companies by company id. What I'm trying to do is select only companies that have press releases within the last for weeks. What the above is giving me is a list of companuid's, whihc does not work. Any help or suggestion would be much appreciated. At first glance, my initial suggestion would be to use IN instead of = for your comparison: AND companyid IN (SELECT ... Also, there's no need to use ORDER BY in your subquery, and frankly I'd probably write this using a JOIN instead of a subquery anyway. SELECT c.* FROMcompanies c INNER JOIN releases r ON c.companyid = r.companyid WHERE c.companyhide = 0 AND r.date_entered BETWEEN DATE_SUB(curdate(), INTERVAL 8 day) AND DATE_SUB(curdate(), INTERVAL 30 day) ORDER BYc.rank, c.company Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208507 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Help Please
and companyid in (select distinct companyid Matthew Small Web Developer American City Business Journals 704-973-1045 [EMAIL PROTECTED] That's it! I forget about in. I knew it was simple. Thanks! -jeff -Original Message- From: Jeff Fongemie [mailto:[EMAIL PROTECTED] Sent: Friday, June 03, 2005 10:20 AM To: CF-Talk Subject: SQL Help Please I've got a query that I just can't get right. select * From companies where companyhide = 0 and companyid = (select distinct companyid From releases where date_entered = DATE_SUB(curdate (),INTERVAL 8 day) and date_entered = DATE_SUB (curdate(),INTERVAL 30 day) order by date_entered desc) order by rank, company Essentially, I've got a table of companies and anothr table with press releases. press releases are linked to companies by company id. What I'm trying to do is select only companies that have press releases within the last for weeks. What the above is giving me is a list of companuid's, whihc does not work. Any help or suggestion would be much appreciated. -jeff ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208512 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL help - inserting a dynamic variable
You've more or less posted the answer. The method to use would be: INSERT INTO tblContactAddress(contactID,addressTypeID,address1, etc.) SELECT #contactIDValue#, ID, '#address1Value#',etc. FROM tblAddressType WHERE typename = 'General' Just make sure typename = 'General' will only return one value or it will insert those values each time it finds 'General' in tblAddressType (i.e. if you have 3 typename = 'General' it will insert 3 records) I'm making life harder on myself than I need to. Salient facts: CFMX7, SQL Server 2000 I'm trying to insert an address. The contact address table has the usual fields, i.e. id, contactID, address1, address2, city, state, zip, etc., but it also has an addressTypeID that is a lookup column that references values in an addressType table that has typeid, typename What I _WANT_ to be able to do is something like this: INSERT INTO tblContactAddress(contactID,addressTypeID,address1, etc.) VALUES (#contactIDValue#, (SELECT ID FROM tblAddressType WHERE typename = 'General'), '#address1Value#',etc.) Of course, I would be using cfqueryparam for all variables. SQL Server doesn't like this. Is there a way to accomplish what I'm after? Thanks! Pete ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202200 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL help - inserting a dynamic variable
Oh, so IOW I can only do that for one join table, not more than one? I have a couple of lookup columns that I'll be inserting... Pete On Apr 11, 2005 12:54 PM, Gareth Arch [EMAIL PROTECTED] wrote: You've more or less posted the answer. The method to use would be: INSERT INTO tblContactAddress(contactID,addressTypeID,address1, etc.) SELECT #contactIDValue#, ID, '#address1Value#',etc. FROM tblAddressType WHERE typename = 'General' Just make sure typename = 'General' will only return one value or it will insert those values each time it finds 'General' in tblAddressType (i.e. if you have 3 typename = 'General' it will insert 3 records) I'm making life harder on myself than I need to. Salient facts: CFMX7, SQL Server 2000 I'm trying to insert an address. The contact address table has the usual fields, i.e. id, contactID, address1, address2, city, state, zip, etc., but it also has an addressTypeID that is a lookup column that references values in an addressType table that has typeid, typename What I _WANT_ to be able to do is something like this: INSERT INTO tblContactAddress(contactID,addressTypeID,address1, etc.) VALUES (#contactIDValue#, (SELECT ID FROM tblAddressType WHERE typename = 'General'), '#address1Value#',etc.) Of course, I would be using cfqueryparam for all variables. SQL Server doesn't like this. Is there a way to accomplish what I'm after? Thanks! Pete ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202206 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL help - inserting a dynamic variable
You should be able to join as many tables as you like (just as in a regular query). Just select the column names you need insert into tblthis (columnone, columntwo, columnthree) select c.dataone, d.datatwo, e.datathree from mycolumns c inner join mycolumnstwo d on (c.my_id = d.my_id) inner join mycolumnsthree e on (c.my_id = e.my_id) where c.typename = 'General' Oh, so IOW I can only do that for one join table, not more than one? I have a couple of lookup columns that I'll be inserting... Pete ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202213 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Help Needed
This isn't how it works (or how BarneyB explained it). The statement is always INSERT INTO, followed by either VALUES or a SELECT statement. The select statement can have any result, as long as it has the same number of columns and the column datatypes match. So, your statement should probably look like this: INSERT INTO tblProducts (ProdName, ProdDesc, ProdSRP, ProdPrice, ProdCost, SubcatID) SELECT name, description, srp, price, cost, subcategoryID FROM dolls_backup Pascal -Original Message- From: Donna French [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 23:13 To: CF-Talk Subject: Re: SQL Help Needed Okay, here's what I've come up with we'll see how it goes... SELECT lanesID, subcategoryID, name, availability, price, srp, cost, description FROM dolls_backup INSERT INTO tblProducts(ProdName, ProdDesc, ProdSRP, ProdPrice, ProdCost, SubcatID) VALUES (dolls_backup.name, dolls_backup.description, dolls_backup.srp, dolls_backup.cost, dolls_backup.subcategoryID) ~ Donna ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184846 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL Help Needed
You'll have to check on the exact syntax, but something like this should work: INSERT INTO myTable (col1, col2, col3) SELECT col4, col5, col6 FROM otherTable The SELECT can be as complex as you want, as long as the columns it returns are the same number and type as what is needed by the INSERT portion. cheers, barneyb On Thu, 18 Nov 2004 15:33:40 -0600, Donna French [EMAIL PROTECTED] wrote: I am redesigning an SQL database and need to know if it's possible to select specific tables/columns from the original into specific tables/columns in the new database. I know I can select the columns I want but moving them into a specified column in the new db that isn't the same structure is what I'm not sure of. Any help appreciated. -- -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/blog/ ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184820 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL Help Needed
Okay, here's what I've come up with we'll see how it goes... SELECT lanesID, subcategoryID, name, availability, price, srp, cost, description FROM dolls_backup INSERT INTO tblProducts(ProdName, ProdDesc, ProdSRP, ProdPrice, ProdCost, SubcatID) VALUES (dolls_backup.name, dolls_backup.description, dolls_backup.srp, dolls_backup.cost, dolls_backup.subcategoryID) ~ Donna On Thu, 18 Nov 2004 13:49:06 -0800, Barney Boisvert [EMAIL PROTECTED] wrote: You'll have to check on the exact syntax, but something like this should work: INSERT INTO myTable (col1, col2, col3) SELECT col4, col5, col6 FROM otherTable The SELECT can be as complex as you want, as long as the columns it returns are the same number and type as what is needed by the INSERT portion. cheers, barneyb On Thu, 18 Nov 2004 15:33:40 -0600, Donna French [EMAIL PROTECTED] wrote: I am redesigning an SQL database and need to know if it's possible to select specific tables/columns from the original into specific tables/columns in the new database. I know I can select the columns I want but moving them into a specified column in the new db that isn't the same structure is what I'm not sure of. Any help appreciated. -- -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/blog/ ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184823 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL help needed fast
On Friday 05 Nov 2004 21:00 pm, Eric Creese wrote: lines is an int and this is MySQL Database. No issues with it in access or sql server Are those escaped ' meant to be there, or did it just happen when you pasted into your mail client ? What happens if you try the query by hand ? also in an order by clause in MySQl how many columns can be sorted at one time? Lots. -- Tom Chiverton Advanced ColdFusion Programmer Tel: +44 (0)1749 834900 email: [EMAIL PROTECTED] BlueFinger Limited Underwood Business Park Wookey Hole Road, WELLS. BA5 1AF Tel: +44 (0)1749 834900 Fax: +44 (0)1749 834XXX web: www.bluefinger.com Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple Quay, BRISTOL. BS1 6EG *** This E-mail contains confidential information for the addressee only. If you are not the intended recipient, please notify us immediately. You should not use, disclose, distribute or copy this communication if received in error. No binding contract will result from this e-mail until such time as a written document is signed on behalf of the company. BlueFinger Limited cannot accept responsibility for the completeness or accuracy of this message as it has been transmitted over public networks.*** ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183603 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL help needed fast
I am not sure by what you mean about escaping but that was the read out from the error page. SELECT * FROM p1_matrix WHERE display=apos;yesapos; AND lines = 1 ORDER BY Charht ; I have done this successfully with Access. I did notice that the datatype for charht is decimal and for lines it is int, so I am not sure if that is the problem or not. ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183623 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL help needed fast
which db, what is the datatype of lines?
On Fri, 5 Nov 2004 14:34:46 -0600, Eric Creese [EMAIL PROTECTED] wrote:
Can some one please tell me why this does not work? If you pass the url.inch
variable it works fine. If you pass the url.line variable it fails with the
following error. code is below the error I need an answer quick please
Syntax error or access violation: You have an error in your SQL syntax near 'lines =
1 ORDER BY Charht ' at line 8 The error occurred in
/home/auto1/autocomm-inc.com/html/dev/matrix.cfm: line 3
1 : cfinclude template=header2.cfm
2 :
3 : cfquery name=getmatrix datasource=yeuwes
4 : SELECT *
5 : FROM p#url.prodid#_matrix
SELECT * FROM p1_matrix WHERE display=apos;yesapos; AND lines = 1 ORDER BY Charht ;
cfquery name=getmatrix datasource=autocomm
SELECT *
FROM p#url.prodid#_matrix
WHERE display='yes'
cfif url.prodid EQ 1
cfif isdefined('url.inch')
cfif url.inch EQ 1
AND charht 2
cfelseif url.inch EQ 2
AND charht 3
AND charht =2
cfelseif url.inch EQ 3
AND charht 4
AND charht =3
cfelseif url.inch EQ 4
AND charht 7
AND charht =4
cfelseif url.inch EQ 7
AND charht =7
/cfif
/cfif
cfif isdefined('url.line')
cfif url.line EQ 1
AND lines = 1
cfelseif url.line EQ 2
AND lines =2
cfelseif url.line EQ 3
AND lines =3
cfelseif url.line EQ 4
AND lines =4
/cfif
/cfif
ORDER BY Charht
cfelseif url.prodid EQ 6
cfif isdefined('url.inch')
cfif url.inch EQ 5
AND charht 6
cfelseif url.inch EQ 8
AND charht 9
AND charht =6
cfelseif url.inch EQ 18
AND charht 19
AND charht =9
cfelseif url.inch EQ 29
AND charht 30
AND charht =19
cfelseif url.inch EQ 30
AND charht =30
/cfif
/cfif
cfif isdefined('url.line')
cfif url.line EQ 1
AND lines = 1
cfelseif url.line EQ 2
AND lines =2
cfelseif url.line EQ 3
AND lines =3
cfelseif url.line EQ 4
AND lines =4
/cfif
/cfif
ORDER BY charht
cfelseif url.prodid EQ 19
cfif isdefined('url.type')
cfif url.type EQ 'baseball'
AND event ='baseball'
cfelseif url.type EQ 'basketball'
AND (event = 'basketball'
OR event='Bsktbll/Wrstlng/Vollyball'
OR event='Possession Indicator'
OR event='Foul Panels'
OR event='Shot Clock')
cfelseif url.type EQ 'football'
AND event ='football'
cfelseif url.type EQ 'hockey'
AND event ='hockey'
cfelseif url.type EQ 'BWV'
AND event ='Bsktbll/Wrstlng/Vollyball'
cfelseif url.type EQ 'race'
AND event ='race track'
cfelseif url.type EQ 'multi'
AND event ='multi-sport'
cfelseif url.type EQ 'soccer'
AND event ='soccer'
/cfif
/cfif
ORDER BY event
/cfif
;
/cfquery
~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183541
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL help needed fast
lines is an int and this is MySQL Database. No issues with it in access or sql server
also in an order by clause in MySQl how many columns can be sorted at one time?
-Original Message-
From: Qasim Rasheed [mailto:[EMAIL PROTECTED]
Sent: Friday, November 05, 2004 2:49 PM
To: CF-Talk
Subject: Re: SQL help needed fast
which db, what is the datatype of lines?
On Fri, 5 Nov 2004 14:34:46 -0600, Eric Creese [EMAIL PROTECTED] wrote:
Can some one please tell me why this does not work? If you pass the url.inch
variable it works fine. If you pass the url.line variable it fails with the
following error. code is below the error I need an answer quick please
Syntax error or access violation: You have an error in your SQL syntax near 'lines =
1 ORDER BY Charht ' at line 8 The error occurred in
/home/auto1/autocomm-inc.com/html/dev/matrix.cfm: line 3
1 : cfinclude template=header2.cfm
2 :
3 : cfquery name=getmatrix datasource=yeuwes
4 : SELECT *
5 : FROM p#url.prodid#_matrix
SELECT * FROM p1_matrix WHERE display=apos;yesapos; AND lines = 1 ORDER BY Charht ;
cfquery name=getmatrix datasource=autocomm
SELECT *
FROM p#url.prodid#_matrix
WHERE display='yes'
cfif url.prodid EQ 1
cfif isdefined('url.inch')
cfif url.inch EQ 1
AND charht 2
cfelseif url.inch EQ 2
AND charht 3
AND charht =2
cfelseif url.inch EQ 3
AND charht 4
AND charht =3
cfelseif url.inch EQ 4
AND charht 7
AND charht =4
cfelseif url.inch EQ 7
AND charht =7
/cfif
/cfif
cfif isdefined('url.line')
cfif url.line EQ 1
AND lines = 1
cfelseif url.line EQ 2
AND lines =2
cfelseif url.line EQ 3
AND lines =3
cfelseif url.line EQ 4
AND lines =4
/cfif
/cfif
ORDER BY Charht
cfelseif url.prodid EQ 6
cfif isdefined('url.inch')
cfif url.inch EQ 5
AND charht 6
cfelseif url.inch EQ 8
AND charht 9
AND charht =6
cfelseif url.inch EQ 18
AND charht 19
AND charht =9
cfelseif url.inch EQ 29
AND charht 30
AND charht =19
cfelseif url.inch EQ 30
AND charht =30
/cfif
/cfif
cfif isdefined('url.line')
cfif url.line EQ 1
AND lines = 1
cfelseif url.line EQ 2
AND lines =2
cfelseif url.line EQ 3
AND lines =3
cfelseif url.line EQ 4
AND lines =4
/cfif
/cfif
ORDER BY charht
cfelseif url.prodid EQ 19
cfif isdefined('url.type')
cfif url.type EQ 'baseball'
AND event ='baseball'
cfelseif url.type EQ 'basketball'
AND (event = 'basketball'
OR event='Bsktbll/Wrstlng/Vollyball'
OR event='Possession Indicator'
OR event='Foul Panels'
OR event='Shot Clock')
cfelseif url.type EQ 'football'
AND event ='football'
cfelseif url.type EQ 'hockey'
AND event ='hockey'
cfelseif url.type EQ 'BWV'
AND event ='Bsktbll/Wrstlng/Vollyball'
cfelseif url.type EQ 'race'
AND event ='race track'
cfelseif url.type EQ 'multi'
AND event ='multi-sport'
cfelseif url.type EQ 'soccer'
AND event ='soccer'
/cfif
/cfif
ORDER BY event
/cfif
;
/cfquery
~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183545
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Help: nvarchar vs varchar
Microsoft SQL Server Help ... F1 .. Unicode Data Traditional non-Unicode data types in Microsoft(r) SQL Server(tm) 2000 allow the use of characters that are defined by a particular character set. A character set is chosen during SQL Server Setup and cannot be changed. Using Unicode data types, a column can store any character defined by the Unicode Standard, which includes all of the characters defined in the various character sets. Unicode data types take twice as much storage space as non-Unicode data types. Unicode data is stored using the nchar, nvarchar, and ntext data types in SQL Server. Use these data types for columns that store characters from more than one character set. Use nvarchar when a column's entries vary in the number of Unicode characters (up to 4,000) they contain. Use nchar when every entry for a column has the same fixed length (up to 4,000 Unicode characters). Use ntext when any entry for a column is longer than 4,000 Unicode characters. NoteThe SQL Server Unicode data types are based on the National Character data types in the SQL-92 standard. SQL-92 uses the prefix character n to identify these data types and values. Micha Schopman Software Engineer Modern Media, Databankweg 12 M, 3821 ALAmersfoort Tel 033-4535377, Fax 033-4535388 KvK Amersfoort 39081679, Rabo 39.48.05.380 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL help!
That would work, but if you have more than a thousand or so items in the sub-select, you're query will not return the expected results.Some databases limit the list length for the IN clause to 1,000 items. Better to do it this way SELECT t1.id FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.id = t2.id WHERE t2.id IS NULL (Oracle) SELECT t1.id FROM table1 t1, table2 t2 WHERE t1.id = t2.id(+) AND t2.id IS NULL +---+ Bryan Love Database Analyst Macromedia Certified Professional Internet Application Developer TeleCommunication Systems [EMAIL PROTECTED] +---+ ...'If there must be trouble, let it be in my day, that my child may have peace'... - Thomas Paine, The American Crisis Let's Roll - Todd Beamer, Flight 93 -Original Message- From: Phillip Beazley [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 10, 2004 12:23 PM To: CF-Talk Subject: Re: SQL help! At 03:18 PM 8/10/2004, you wrote: This is probably basic, but my brain is not functioning at this point today... I need to compare two tables. They both contain customer numbers. One table mimics the other so the same data *should* be in each. It has come to my attention that there are orphans in one of the tables. What I'd like to do is compare one table against the other, find the orphans in that table and list them out. Then vice versa with the other table. I was attempting to do a Inner join but it didn't seem to work. Anyone with ideas? You could do something like... SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2) SELECT * FROM table2 WHERE id NOT IN (SELECT id FROM table1) Not super-fab, but it'll show you what's in one that isn't in the other... -- Phillip Beazley Onvix -- Website Hosting, Development E-commerce Visit http://www.onvix.com/ or call 727-578-9600. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL help!
At 03:18 PM 8/10/2004, you wrote: This is probably basic, but my brain is not functioning at this point today... I need to compare two tables. They both contain customer numbers. One table mimics the other so the same data *should* be in each. It has come to my attention that there are orphans in one of the tables. What I'd like to do is compare one table against the other, find the orphans in that table and list them out. Then vice versa with the other table. I was attempting to do a Inner join but it didn't seem to work. Anyone with ideas? You could do something like... SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2) SELECT * FROM table2 WHERE id NOT IN (SELECT id FROM table1) Not super-fab, but it'll show you what's in one that isn't in the other... -- Phillip Beazley Onvix -- Website Hosting, Development E-commerce Visit http://www.onvix.com/ or call 727-578-9600. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL help!
Quick and dirty .and it worked like a charm.. Thanks IN was where I shoulda been looking... Jeff [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL help!
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2) SELECT * FROM table2 WHERE id NOT IN (SELECT id FROM table1) Yes, and may be UNION the two in one: SELECT id FROM table1 WHERE id NOT IN (SELECT id FROM table2) UNION SELECT id FROM table2 WHERE id NOT IN (SELECT id FROM table1) However, if the tables are big tables, this might kill the server, so may be using LEFT JOINs could be more efficient in this case: SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id = table2.id WHERE table2.id IS NULL I didn't test it, but this should select records in table1 that have no match in table2 Then invert the quey for the othe table. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL help!
That union worked well too... thanks! Jeff [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL Help Please?
Maybe I'm misunderstanding what you are trying to do, but try this Select count(Referer) as refcount From myTable Where Referer = #myUserID# Cutter Dave Phillips wrote: Hi guys, I hope this isn't too far off topic, but I'm stuck on an SQL issue and really need some help. I have a table with these two fields: UserID(int) Referer (int) UserID is obviously my user's ID number.Referer refers to the UserID of the person who referred them. I need to run an SQL Query that returns to me the total number of records in which a UserID is in the Referer. For example, Ted is UserID 1 and John is UserID 2 Ted Refers, Jan, Jerry and Jack John Refers Mary and Bill I now have 7 records, all unique UserID's but the referer for Jan, Jerry and Jack is 1 and the referer for Mary and Bill is 2 I want to run an SQL Query that returns the following results: UserIDNumReferred 13 22 I know it's GOT to be possible to do it all in SQl.I know I can do it in CF with no problem by looping over my userid list, but that takes way too long. Any ideas? Thanks! Dave [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Help Please?
Unfortunately, that will only give me the number of referer's for ONE UserID.I need the number of referer's for EACH of the User's returned in one SQL Statement. Thanks though. Sincerely, Dave Phillips 94percent.com [EMAIL PROTECTED] 615-746-3851 Why do 100% of the work when we'll do 94% of it for you? - http://honor.94percent.com _ From: Cutter (CF-Talk) [mailto:[EMAIL PROTECTED] Sent: Friday, July 30, 2004 8:31 PM To: CF-Talk Subject: Re: SQL Help Please? Maybe I'm misunderstanding what you are trying to do, but try this Select count(Referer) as refcount From myTable Where Referer = #myUserID# Cutter Dave Phillips wrote: Hi guys, I hope this isn't too far off topic, but I'm stuck on an SQL issue and really need some help. I have a table with these two fields: UserID(int) Referer (int) UserID is obviously my user's ID number.Referer refers to the UserID of the person who referred them. I need to run an SQL Query that returns to me the total number of records in which a UserID is in the Referer. For example, Ted is UserID 1 and John is UserID 2 Ted Refers, Jan, Jerry and Jack John Refers Mary and Bill I now have 7 records, all unique UserID's but the referer for Jan, Jerry and Jack is 1 and the referer for Mary and Bill is 2 I want to run an SQL Query that returns the following results: UserIDNumReferred 13 22 I know it's GOT to be possible to do it all in SQl.I know I can do it in CF with no problem by looping over my userid list, but that takes way too long. Any ideas? Thanks! Dave _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Help Please?
This is what you need ... SELECT DISTINCT(referer), COUNT(referer) FROM users GROUP BY referer -- Jeff _ From: Dave Phillips [mailto:[EMAIL PROTECTED] Sent: Friday, July 30, 2004 8:37 PM To: CF-Talk Subject: RE: SQL Help Please? Unfortunately, that will only give me the number of referer's for ONE UserID.I need the number of referer's for EACH of the User's returned in one SQL Statement. Thanks though. Sincerely, Dave Phillips 94percent.com [EMAIL PROTECTED] 615-746-3851 Why do 100% of the work when we'll do 94% of it for you? - http://honor.94percent.com _ From: Cutter (CF-Talk) [mailto:[EMAIL PROTECTED] Sent: Friday, July 30, 2004 8:31 PM To: CF-Talk Subject: Re: SQL Help Please? Maybe I'm misunderstanding what you are trying to do, but try this Select count(Referer) as refcount From myTable Where Referer = #myUserID# Cutter Dave Phillips wrote: Hi guys, I hope this isn't too far off topic, but I'm stuck on an SQL issue and really need some help. I have a table with these two fields: UserID(int) Referer (int) UserID is obviously my user's ID number.Referer refers to the UserID of the person who referred them. I need to run an SQL Query that returns to me the total number of records in which a UserID is in the Referer. For example, Ted is UserID 1 and John is UserID 2 Ted Refers, Jan, Jerry and Jack John Refers Mary and Bill I now have 7 records, all unique UserID's but the referer for Jan, Jerry and Jack is 1 and the referer for Mary and Bill is 2 I want to run an SQL Query that returns the following results: UserIDNumReferred 13 22 I know it's GOT to be possible to do it all in SQl.I know I can do it in CF with no problem by looping over my userid list, but that takes way too long. Any ideas? Thanks! Dave _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Help Please?
Jeff, THANKS!I knew it could be done!I experimented with DISTINCT and COUNT and GROUP BY over and over but couldn't find the right combination. Thanks so much! Sincerely, Dave Phillips 94percent.com [EMAIL PROTECTED] 615-746-3851 Why do 100% of the work when we'll do 94% of it for you? - http://honor.94percent.com _ From: Jeff Chastain [mailto:[EMAIL PROTECTED] Sent: Friday, July 30, 2004 8:39 PM To: CF-Talk Subject: RE: SQL Help Please? This is what you need ... SELECT DISTINCT(referer), COUNT(referer) FROM users GROUP BY referer -- Jeff _ From: Dave Phillips [mailto:[EMAIL PROTECTED] Sent: Friday, July 30, 2004 8:37 PM To: CF-Talk Subject: RE: SQL Help Please? Unfortunately, that will only give me the number of referer's for ONE UserID.I need the number of referer's for EACH of the User's returned in one SQL Statement. Thanks though. Sincerely, Dave Phillips 94percent.com [EMAIL PROTECTED] 615-746-3851 Why do 100% of the work when we'll do 94% of it for you? - http://honor.94percent.com _ From: Cutter (CF-Talk) [mailto:[EMAIL PROTECTED] Sent: Friday, July 30, 2004 8:31 PM To: CF-Talk Subject: Re: SQL Help Please? Maybe I'm misunderstanding what you are trying to do, but try this Select count(Referer) as refcount From myTable Where Referer = #myUserID# Cutter Dave Phillips wrote: Hi guys, I hope this isn't too far off topic, but I'm stuck on an SQL issue and really need some help. I have a table with these two fields: UserID(int) Referer (int) UserID is obviously my user's ID number.Referer refers to the UserID of the person who referred them. I need to run an SQL Query that returns to me the total number of records in which a UserID is in the Referer. For example, Ted is UserID 1 and John is UserID 2 Ted Refers, Jan, Jerry and Jack John Refers Mary and Bill I now have 7 records, all unique UserID's but the referer for Jan, Jerry and Jack is 1 and the referer for Mary and Bill is 2 I want to run an SQL Query that returns the following results: UserIDNumReferred 13 22 I know it's GOT to be possible to do it all in SQl.I know I can do it in CF with no problem by looping over my userid list, but that takes way too long. Any ideas? Thanks! Dave _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Help
You are filtering on the enrollment table. This makes the left outer join useless. Try doing : WHERE e.EnrollmentDate BETWEEN '#fromDate#' AND '#toDate#' ORe.EnrollmentDate IS NULL And of course (Guess what or look at some other threads right now) -Original Message- From: Venable, John [mailto:[EMAIL PROTECTED] Sent: woensdag 16 juni 2004 18:25 To: CF-Talk Subject: SQL Help Hey Folks, I've got a query that I'm trying to pull some aggregate data from and refer to it with array syntax. i.e. #queryname.field[i]# One query I have isn't pulling all the values that I might encounter, thereby throwing the index off for any references thereafter. here's my query: SELECT d.Disability, count(ed.DisabilityID) as Count FROM Disabilities d LEFT OUTER JOIN EnrollmentsDisabilities ed ON d.DisabilityID = ed.DisabilityID JOIN Enrollments e ON ed.ProfileID = e.ProfileID AND ed.EnrollmentNumber = e.EnrollmentNumber WHERE e.EnrollmentDate BETWEEN '#fromDate#' AND '#toDate#' GROUP BY Disability ORDER BY Disability EnrollmentsDisabilities is a join table for a many-to-many relationship with Enrollments and Disabilities I want all the possible values of Disability even if the count is zero. I thought the 'LEFT OUTER JOIN' syntax would take care of that, but it's not working. What am I doing wrong? Thanks much! John --- John Venable Director of Web Architecture Epilepsy Foundation [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL Help
yeah yeah, the cfqueryparam, i pulled this from SQL Analyzer, it chokes for some reason on that... :-) thanks, i'll check this out. JOhn -Original Message- From: Pascal Peters [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 16, 2004 2:12 PM To: CF-Talk Subject: RE: SQL Help You are filtering on the enrollment table. This makes the left outer join useless. Try doing : WHERE e.EnrollmentDate BETWEEN '#fromDate#' AND '#toDate#' ORe.EnrollmentDate IS NULL And of course (Guess what or look at some other threads right now) -Original Message- From: Venable, John [mailto:[EMAIL PROTECTED] Sent: woensdag 16 juni 2004 18:25 To: CF-Talk Subject: SQL Help Hey Folks, I've got a query that I'm trying to pull some aggregate data from and refer to it with array syntax. i.e. #queryname.field[i]# One query I have isn't pulling all the values that I might encounter, thereby throwing the index off for any references thereafter. here's my query: SELECT d.Disability, count(ed.DisabilityID) as Count FROM Disabilities d LEFT OUTER JOIN EnrollmentsDisabilities ed ON d.DisabilityID = ed.DisabilityID JOIN Enrollments e ON ed.ProfileID = e.ProfileID AND ed.EnrollmentNumber = e.EnrollmentNumber WHERE e.EnrollmentDate BETWEEN '#fromDate#' AND '#toDate#' GROUP BY Disability ORDER BY Disability EnrollmentsDisabilities is a join table for a many-to-many relationship with Enrollments and Disabilities I want all the possible values of Disability even if the count is zero. I thought the 'LEFT OUTER JOIN' syntax would take care of that, but it's not working. What am I doing wrong? Thanks much! John --- John Venable Director of Web Architecture Epilepsy Foundation _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL HELP!
Ascension day. -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: vrijdag 21 mei 2004 3:07 To: CF-Talk Subject: RE: SQL HELP! what holiday? tony [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL HELP!
SELECT stuff.*, person1.*, person2.*, person3.* FROM( ( stuff LEFT JOIN persons AS person1 ON stuff.first_person_id = persons.person_id ) LEFT JOIN persons AS person2 ON stuff.second_person_id = persons.person_id ) LEFT JOIN persons AS person3 ON stuff.third_person_id = persons.person_id) _ From: brobborb [mailto:[EMAIL PROTECTED] Sent: Thursday, 20 May 2004 5:55 p.m. To: CF-Talk Subject: SQL HELP! hey guys, i have a table named Stuff.this table has the fields stuff_id, stuff_title, first_person_id, second_person_id, and third_person_id. The those last 3 fields refer to a table called persons, and contains the person_id.The table PERSONS contain the fields person_id, first_name, and last_name Now I want to return all the rows in the STUFF table, but to replace the last 3 fields with names in the PERSONS table.THe fields first_person_id, second_person_id, and third_person_id links to the person_id field in the table PERSONS. Yes, I admit this DB is poorly designed, but it's too late too change it now and risk breaking up other stuff, so I just need a quick SQL solution. Please help! Thanks _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL HELP!
What if I wanted to add a WHERE clause? - Original Message - From: Matthew Walker To: CF-Talk Sent: Thursday, May 20, 2004 1:28 AM Subject: RE: SQL HELP! SELECT stuff.*, person1.*, person2.*, person3.* FROM( ( stuff LEFT JOIN persons AS person1 ON stuff.first_person_id = persons.person_id ) LEFT JOIN persons AS person2 ON stuff.second_person_id = persons.person_id ) LEFT JOIN persons AS person3 ON stuff.third_person_id = persons.person_id) _ From: brobborb [mailto:[EMAIL PROTECTED] Sent: Thursday, 20 May 2004 5:55 p.m. To: CF-Talk Subject: SQL HELP! hey guys, i have a table named Stuff.this table has the fields stuff_id, stuff_title, first_person_id, second_person_id, and third_person_id. The those last 3 fields refer to a table called persons, and contains the person_id.The table PERSONS contain the fields person_id, first_name, and last_name Now I want to return all the rows in the STUFF table, but to replace the last 3 fields with names in the PERSONS table.THe fields first_person_id, second_person_id, and third_person_id links to the person_id field in the table PERSONS. Yes, I admit this DB is poorly designed, but it's too late too change it now and risk breaking up other stuff, so I just need a quick SQL solution. Please help! Thanks _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL HELP!
Just add it after the FROM clause. Also keep in mind that if you use all * in the SELECT clause, you won't be able to access all names in CF. You have to use aliasses for the names SELECT stuff.*, person1.first_name AS first_name1, person1.last_name AS last_name1, person2.first_name AS first_name2, person2.last_name AS last_name2, person2.first_name AS first_name2, person1.last_name AS last_name2 FROMstuff LEFT JOIN persons AS person1 ON stuff.first_person_id = persons.person_id LEFT JOIN persons AS person2 ON stuff.second_person_id = persons.person_id LEFT JOIN persons AS person3 ON stuff.third_person_id = persons.person_id WHERE ... (your where clause here) -Original Message- From: brobborb [mailto:[EMAIL PROTECTED] Sent: donderdag 20 mei 2004 9:07 To: CF-Talk Subject: Re: SQL HELP! What if I wanted to add a WHERE clause? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL HELP!
hey peter may i contact you off the list quick? - Original Message - From: Pascal Peters To: CF-Talk Sent: Thursday, May 20, 2004 4:22 AM Subject: RE: SQL HELP! Just add it after the FROM clause. Also keep in mind that if you use all * in the SELECT clause, you won't be able to access all names in CF. You have to use aliasses for the names [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL HELP!
From: brobborb Now I want to return all the rows in the STUFF table, but to replace the last 3 fields with names in the PERSONS table. THe fields first_person_id, second_person_id, and third_person_id links to the person_id field in the table PERSONS. A good book to have on your desk for stuff like this is Teach Yourself SQL in 10 Minutes by Ben Forta It covers just about everything you need to know for most SQL statements You can also use the database tools, such as the Query builder in Access or the View builder in Enterprise Manager - these will make the joins for you, and you can examine the SQL to see how it's doing it As long as you don't mind learning by example, then you can pick up the basics really quickly this way [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL HELP!
Maybe too late, but you can. It's a holiday in Belgium, so I don't read my mail as often as on a work day. Pascal -Original Message- From: brobborb [mailto:[EMAIL PROTECTED] Sent: donderdag 20 mei 2004 11:24 To: CF-Talk Subject: Re: SQL HELP! hey peter may i contact you off the list quick? - Original Message - From: Pascal Peters To: CF-Talk Sent: Thursday, May 20, 2004 4:22 AM Subject: RE: SQL HELP! Just add it after the FROM clause. Also keep in mind that if you use all * in the SELECT clause, you won't be able to access all names in CF. You have to use aliasses for the names [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL HELP!
what holiday? tony Tony Weeg sr. web applications architect navtrak, inc. [EMAIL PROTECTED] 410.548.2337 www.navtrak.net -Original Message- From: Pascal Peters [mailto:[EMAIL PROTECTED] Sent: Thursday, May 20, 2004 11:19 AM To: CF-Talk Subject: RE: SQL HELP! Maybe too late, but you can. It's a holiday in Belgium, so I don't read my mail as often as on a work day. Pascal -Original Message- From: brobborb [mailto:[EMAIL PROTECTED] Sent: donderdag 20 mei 2004 11:24 To: CF-Talk Subject: Re: SQL HELP! hey peter may i contact you off the list quick? - Original Message - From: Pascal Peters To: CF-Talk Sent: Thursday, May 20, 2004 4:22 AM Subject: RE: SQL HELP! Just add it after the FROM clause. Also keep in mind that if you use all * in the SELECT clause, you won't be able to access all names in CF. You have to use aliasses for the names [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
