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:325658
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

2009-08-25 Thread Kevin Roche


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

2009-08-25 Thread Kevin Roche


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

2009-08-25 Thread Dawson, Michael


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

2009-08-25 Thread Billy Cox


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

2009-08-25 Thread Brian Kotek


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

2009-06-30 Thread Dominic Watson


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

2009-06-30 Thread Stephane Vantroyen


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

2009-06-30 Thread James Holmes


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

2009-06-30 Thread Scott Brady


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

2009-06-30 Thread Dave Watts


 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

2009-06-30 Thread James Holmes


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

2009-06-30 Thread James Holmes


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

2009-06-30 Thread Justin Scott


 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

2009-06-30 Thread Dave Watts


 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

SQL Help

2009-06-28 Thread Jason Slack


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:324026
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

2009-06-28 Thread Charlie Griefer


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

2009-06-28 Thread Jason Slack


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

2009-06-28 Thread Matt Quackenbush


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

SQL help..

2008-10-29 Thread cf coder

Hello there,

I do appreciate this isn't the right forum for posting this question but I have 
posted my question in the SQL forum but I haven't heard anything back. It's a 
very straightforward query... at least I hope it is for someone with good sql 
skills.

I was hoping someone could help here. I have a table called 'phone_numbers' 
with two columns, one called 'person_name' and the other called 'number'. A 
person can have more than one number and I want the query to return all 
person's that have more than 3 numbers.

Jamie   1
Jamie   2
Jamie   3
Jamie   4
Allan   5
Vicki   6
Marilyn 7
Paul8
Scott   9
Scott   10
Scott   11
Scott   12

So in the above example, the query would return Jamie and Scott. How do I get 
this data?

Regards,
John 

~|
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:314540
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..

2008-10-29 Thread Peter Boughton

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..

2008-10-29 Thread cf coder

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

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:312573
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

2008-09-16 Thread Gert Franz

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

2008-09-16 Thread Rick Faircloth

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

2008-09-16 Thread Jim Wright

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

2008-09-16 Thread Rick Faircloth

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

2008-09-16 Thread Jim Wright

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

2008-09-16 Thread Judah McAuley

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

2008-09-16 Thread Justin Scott

 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

2008-04-03 Thread Paul Ihrig

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

sql help NEQ

2008-04-02 Thread Paul Ihrig

ok i have 2 views
V_riprod_ZMATMAST returns 60 records
V_riprod_Specs_ZMATMAST_EQ returns  39 records or so that are matched
in another table.
these i will do an update on

problem i am having is the NEQ 
i am trying to use the view below to return all the results of the
records that don't match.
so i can do an Insert...

SELECT DISTINCT TOP 100 PERCENT ABS(dbo.V_riprod_ZMATMAST.sap_partnum) AS pnum2
FROM dbo.V_riprod_ZMATMAST INNER 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_ZMATMAST.sap_partnum 
N'dbo.V_riprod_Specs_ZMATMAST_EQ.sap_partnum')

i am using mssql2000 ent viewer.
have tried left outer join  a bunch of wierd stuff, and just keep
getting funky results...


This works for counting
rcdCT   TotalCount  EqCount NeqCount
1  60  39   21

SELECT TOP 100 PERCENT COUNT(*) AS rcdCT,
dbo.V_riprod_Specs_ZMATMAST_Count.rcdCT AS TotalCount,
  dbo.V_riprod_Specs_ZMATMAST_EQ_Count.rcdCT AS EqCount,
  dbo.V_riprod_Specs_ZMATMAST_Count.rcdCT -
dbo.V_riprod_Specs_ZMATMAST_EQ_Count.rcdCT AS NeqCount
FROM dbo.V_riprod_Specs_ZMATMAST_Count CROSS JOIN
  dbo.V_riprod_Specs_ZMATMAST_EQ_Count
GROUP BY dbo.V_riprod_Specs_ZMATMAST_Count.rcdCT,
dbo.V_riprod_Specs_ZMATMAST_EQ_Count.rcdCT

~|
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:302515
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!!!!!!!!!!

2007-11-23 Thread Dave l

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!!!!!!!!!!

2007-11-23 Thread Will Tomlinson

 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

sql help!!!!!!!!!!

2007-11-23 Thread Dave l

ok i need to put Upsell items underneath the add upsell items form, so that 
they can see what upsell products they currently have for that product.

(mysql 5 btw)

So it is all good but then they asked for me to make it sort-able and that is 
causing me a headache and can't quite get that last bit in there correctly.

3 tables and the fields I need from them

upsell
upsell_id, product_id, upsell_product_id, upsell_sort

products
product_id, brand_id, product_item

brands
brand_id, brand_name

product id is taken from url
upsell_product_id is just a second product_id field(so product_id might be 19 
and upsell_product_id might be 25, which is just actually product 25)

So i get and filter the actual product by url.id then I need to grab any 
matching records in the upsell table and get the upsell_product_id and then 
match that to a product in the product table and then match that to a brand in 
the brand table.

---

On the page I am showing this

Brand|  Product|  Sort  |  Delete

I can use this to get close to what I need but it doesn't have the sort from 
upsells and the (20,21) would be a list that I need to get the product info on 
(would need to be from a previous query.

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
fromproducts, brands
where   products.brand_id = brands.brand_id
and products.product_id  in (20,21)
order bybrands.brand_name asc, products.product_item asc

any good suggestions?


~|
ColdFusion 8 - Build next generation apps
today, with easy PDF and Ajax features - download now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293705
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

OT: SQL Help

2007-10-24 Thread Rick Root

I'm having a weird query issue with SQL server...

The following query inserts two rows into the query_results
table:


insert into ENQUIRE.dbo.QUERY_RESULTS ( QUERYID, ENTITY_ID )
SELECT DISTINCT 1933 AS QUERYID, A.ENTITY_ID
FROM BIOTAB1 A
inner join WEBREPORTS.dbo.TB912 TB912 on A.ENTITY_ID=TB912.ENTITYID
--  inner join WEBREPORTS.dbo.TB967 TB967 on A.ENTITY_ID=TB967.ENTITYID
WHERE 1=1
AND A.PRIMARY_RECORD_STATUS IN ('A','-')
AND EXISTS (
SELECT 1 FROM AFFIL X
WHERE X.ENTITY_ID = A.ENTITY_ID AND X.RECORD_TYPE IN ('TS')
)
AND
(
1=0 OR
(
1=1 and TB912.MAILCODE IN ('YAN')
--  ) OR (
--  1=1 and TB967.EVENTCDE IN ('AF55')
)
)


That's correct behavior.  If I run the select statement by
itself, it returns 2 records.

But if I uncomment the three lines that are commented out, the
query only inserts ONE record.

The SELECT statement run by itself (with the commented out
lines uncommented) returns two records.

Why won't it insert the two records?

I'm just running this stuff in SQL Server Management Studio,
but the results from Coldfusion are the same.

Rick

~|
Get involved in the latest ColdFusion discussions, product
development sharing, and articles on the Adobe Labs wiki.
http://labs/adobe.com/wiki/index.php/ColdFusion_8

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291974
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

SQL Help, please ...

2007-06-27 Thread tjones

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






~|
CF 8 â Scorpio beta now available, 
easily build great internet experiences â Try it now on Labs
http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282317
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, please ...

2007-06-27 Thread Brad Wood

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 ...

2007-06-27 Thread Jochem van Dieten

[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

ANYONE? Pretty please?? more SQL help....

2007-04-17 Thread Will Tomlinson

Heck, I did up a special page just to explain my needs better. :)

http://wtomlinson.com/eval.html

been flailing away at this for quite a while now...

Thanks,
Will

~|
ColdFusion MX7 and Flex 2 
Build sales  marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:27
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Need more SQL help....

2007-04-16 Thread Will Tomlinson

Ok, I have one last query to get right in my eval app. 

Here's my schema: http://wtomlinson.com/evalschema.gif

Jim has been kind enough to help work out my queries. Now, I just need to 
rollup my rankings questions and I'm done (mostly). 

The data looks like this:

tblquestions:
Rank these tools in order of learning curve

tblanswersets
CF
..NET
PHP

tblanswers
1
2
3

Answer get inserted into an output table, tblevalanswerresults, where they can 
be counted. 

Here's what I need in plain english:

Show me a list of questions. With each question, show me a list of the answers 
according to what they were ranked, listed highes ranked to lowest. 

Here's a query I'm workin with, the data isn't right tho. It just counts up the 
answers for each answerset. 

cfquery name=getRankedQuestionReport datasource=#SESSION.DSN#
SELECT 
Q.questionID,
Q.questionsetID,
Q.question,
Q.questioncatid,
QAX.questionid,
QAX.answersetid,
ASets.answersetid,
ASets.answersettypeid,
ASets.answersetname,
AST.answersettypename,
ASAX.answersetid,
ASAX.answerid,
A.answerID,
A.answername,
A.answervalue,
(SELECT count(*) FROM tblevalanswerresults e1 INNER JOIN tblevaluations 
e2 ON e1.evalid = e2.evalid
WHERE e2.InstructorID = '#SESSION.CollegeID#' AND e1.answerID = A.answerID AND 
e1.questionid = Q.questionID)
AS totalforthisquestion,
(SELECT count(*) FROM tblevalanswerresults e1 INNER JOIN tblevaluations 
e2 ON e1.evalid = e2.evalid
WHERE e2.InstructorID = '#SESSION.CollegeID#' AND e1.questionid = Q.questionID)
AS totalforinstructor,
((SELECT count(*) FROM tblevalanswerresults e1 INNER JOIN tblevaluations 
e2 ON e1.evalid = e2.evalid
WHERE e2.InstructorID = '#SESSION.CollegeID#' AND e1.answerID = A.answerID AND 
e1.questionid = Q.questionID)/
(SELECT count(*) FROM tblevalanswerresults e1 INNER JOIN tblevaluations 
e2 ON e1.evalid = e2.evalid
WHERE e2.InstructorID = '#SESSION.CollegeID#' AND e1.questionid = 
Q.questionID)*100) 
AS answerpercentage
FROM   tblquestions Q
INNER JOIN tblquestionsanswers_x  QAX ON Q.questionID = QAX.questionID
INNER JOIN tblanswersets ASets ON QAX.answersetid = ASets.answersetid
INNER JOIN tblanswersettypes AST ON AST.answersettypeid = 
ASets.answersettypeid
INNER JOIN tblanswersetsanswers_x ASAX ON QAX.answersetid = ASAX.answersetid
INNER JOIN tblanswers A ON ASAX.answerid = A.answerid
WHERE Q.questioncatid = 3  /* Additional Q = 3*/
AND ASets.answersettypeid = 3  /* Ranking answers only = 3*/
/cfquery 

Thanks much,
Will

~|
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:275469
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

2007-04-04 Thread [EMAIL PROTECTED]

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

SQL Help

2007-04-03 Thread [EMAIL PROTECTED]

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 and Flex 2 
Build sales  marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274476
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

2007-04-03 Thread Greg Morphis

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

2007-04-03 Thread [EMAIL PROTECTED]

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

2007-04-03 Thread Greg Morphis

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

2007-04-03 Thread [EMAIL PROTECTED]

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

2007-04-03 Thread Greg Morphis

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

2007-04-03 Thread Josh Nathanson

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: Need More SQL help - DRIVING ME NUTS!

 Put every field from the select that is not an aggregate in your 
group by and you get something understandable.

Jochem

Jochem,

I did this and it still gives me incorrect results. It gives me a count of 1 
for each answer, no matter what the answer is. I'm just testing with one 
evaluation record to keep it simple. I should still get 0 records returned for 
some of these answers. 

I don't know why I can't simply run a query that says this:

Show me a list of all the questions. With each question, I need all possible 
answers. With each possible answer, show me a count of the ones that were 
selected 

I should be able to get this with a query, but I'm not. I'm missing something. 
ARG!! 

Will

~|
ColdFusion MX7 and Flex 2 
Build sales  marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273245
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: Need More SQL help - DRIVING ME NUTS!

2007-03-21 Thread Adrian Lynch

No been following this thread, but can you do the first two parts?

Show me a list of all the questions. With each question, I need all
possible answers

What about the third part?

With each possible answer, show me a count of the ones that were selected

Do those then try to combine them.

Adrian

-Original Message-
From: Will Tomlinson [mailto:[EMAIL PROTECTED]
Sent: 21 March 2007 10:45
To: CF-Talk
Subject: Re: Need More SQL help - DRIVING ME NUTS!


 Put every field from the select that is not an aggregate in your
group by and you get something understandable.

Jochem

Jochem,

I did this and it still gives me incorrect results. It gives me a count of 1
for each answer, no matter what the answer is. I'm just testing with one
evaluation record to keep it simple. I should still get 0 records returned
for some of these answers.

I don't know why I can't simply run a query that says this:

Show me a list of all the questions. With each question, I need all
possible answers. With each possible answer, show me a count of the ones
that were selected

I should be able to get this with a query, but I'm not. I'm missing
something. ARG!!

Will



~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7  
Flex 2
Free Trial 
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273246
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Need More SQL help - DRIVING ME NUTS!

No been following this thread, but can you do the first two parts?

Yes, I've got this part working with Jochem's fine SQL. 


What about the third part?

With each possible answer, show me a count of the ones that were selected

Nope


Do those then try to combine them.

Adrian

This is where the problem is. I haven't figured out how to combine them, group 
them, whatever needs to be done, I haven't gotten it right yet. I can't quit on 
this until it works. 

Here's the data retrieval as it stands now:
http://wtomlinson.com/evalQRYs/qryDatabig.gif

Here's the data in my many table:

http://wtomlinson.com/evalQRYs/tableData.gif

This shouldn't be hard!! 

Thanks adrian,
Will

~|
ColdFusion MX7 and Flex 2 
Build sales  marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273249
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Need More SQL help - DRIVING ME NUTS!

2007-03-21 Thread Jim Wright

Three things I noted...
1. There are a lot of duplicated columns in the result list...I removed
those to make it easier to read and debug
2. It looks to me like tblevalanswerresults needs to be joined to both
tblquestions and tblanswers...see below
3. When you join on tblEvaluations like you did previously, and then
filter with a WHERE clause, you may get instances where you won't return
some of the questions in a set because none were answered for that
instructor. I moved the filtering on instructor up into the join.

If this doesn't work, just do a dump of each table and post that
somewhere. It will be easier to debug with the whole picture.

SELECT
Q.questionID,Q.questionsetID,Q.question,ARS.answersetname,A.answerID,A.answername,A.answervalue,

COUNT(EAR.answerID) AS numAnswers
FROM tblquestions Q
INNER JOIN tblanswersets ARS USING (answerSetID)
INNER JOIN tblanswers A USING (answerSetID)
LEFT JOIN (tblevalanswerresults EAR
INNER JOIN tblEvaluations E
ON E.evalID = EAR.evalID AND E.InstructorID = '#SESSION.CollegeID#')
ON A.answerID = EAR.answerID AND Q.QuestionID = EAR.QuestionID
GROUP BY
Q.questionID,Q.questionsetID,Q.question,ARS.answersetname,A.answerID,A.answername,A.answervalue

~|
ColdFusion MX7 and Flex 2
Build sales marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT

Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273259
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Need More SQL help - DRIVING ME NUTS!

Thanks a ton Jim! I'll give it a shot later today and let you know the results. 

Thanks!

Will

~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7  
Flex 2
Free Trial 
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273267
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Need More SQL help - DRIVING ME NUTS!

Ok Jim, this is getting the results I'm after. 

The counts are correct, everything looks right so far. I just needta test it 
some more. 

Would you please email me offlist? 

WT at WTOMLINSON.com

Thanks a ton dude!

Will


~|
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:273289
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Need some SQL help!

2007-03-20 Thread Jochem van Dieten

Will Tomlinson wrote:
 
 Show me a list of questions

SELECT ...
FROM tblquestions

 all possible evaluation answers for each question

SELECT ...
FROM
tblquestions
INNER JOIN
tblanswersets USING (answerSetID)
INNER JOIN
tblanswers USING (answerID)

 and a total of each answer chosen. 

SELECT
A.answerID,
COUNT(EAR.answerID)
FROM
tblquestions Q
INNER JOIN
tblanswersets ARS USING (answerSetID)
INNER JOIN
tblanswers A USING (answerSetID)
LEFT JOIN
tblevalanswersresults EAR USING (answerID)
GROUP BY
A.answerID


 Does this make sense?

If you join this to some table with instructors you should get what you 
need.

Jochem

~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7  
Flex 2
Free Trial 
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273070
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Need some SQL help!

 Does this make sense?

If you join this to some table with instructors you should get what you 
need.

Jochem

Yes Jochem. I'm remote right now, but I'll give this a shot later today and 
let'cha know how it turns out. 

Thanks a ton dude!

Will

~|
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:273078
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Need some SQL help!

Jochem,

Do all these individual queries go into one query, separated with a ; ?

Thanks,
Will

~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7  
Flex 2
Free Trial 
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273093
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Need some SQL help!

2007-03-20 Thread Greg Morphis

select *
from (
SELECT ...
FROM tblquestions
) questions,
(
SELECT ...
FROM
   tblquestions
   INNER JOIN
   tblanswersets USING (answerSetID)
   INNER JOIN
   tblanswers USING (answerID)
) answers,
(
SELECT
   A.answerID,
   COUNT(EAR.answerID)
FROM
   tblquestions Q
   INNER JOIN
   tblanswersets ARS USING (answerSetID)
   INNER JOIN
   tblanswers A USING (answerSetID)
   LEFT JOIN
   tblevalanswersresults EAR USING (answerID)
GROUP BY
   A.answerID
) counts
then you join your 3 sub queries down here..

On 3/20/07, Will Tomlinson [EMAIL PROTECTED] wrote:
 Jochem,

 Do all these individual queries go into one query, separated with a ; ?

 Thanks,
 Will

 

~|
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:273110
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Need some SQL help!

2007-03-20 Thread Jochem van Dieten

Will Tomlinson wrote:
 
 Do all these individual queries go into one query, separated with a ; ?

Just run the last one, the others are to show how you build it step by step.

Jochem

~|
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:273113
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Need some SQL help!

Will Tomlinson wrote:
 
 Do all these individual queries go into one query, separated with a ; ?

Just run the last one, the others are to show how you build it step by step.


duhh.. :)

Thanks Jochem!

Will

~|
ColdFusion MX7 and Flex 2 
Build sales  marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273121
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Need some SQL help!

Jochem, this is close but not quite giving me the results I'm after.

Here's the data as it looks in the table. I answered three questions, each had 
a choice of 5 answers. I chose 1 answer for each question. 

Here's the data as it appears in the tblEvalAnswerResults many table. 

http://wtomlinson.com/evalQRYs/tableData.jpg

Here's the qry: 

SELECT
Q.questionID,
Q.questionsetID,
Q.question,
Q.answersetid,
ARS.answersetid,
ARS.answersetname,
A.answersetID,
A.answername, 
A.answersortnum, 
A.answervalue,
EAR.evalID, 
EAR.answerID,
A.answerID, 
COUNT(EAR.answerID)
FROM   tblquestions Q  
INNER JOIN   tblanswersets ARS
 USING (answerSetID)
 INNER JOIN   tblanswers A
 USING (answerSetID)  
 LEFT JOIN  tblevalanswerresults EAR
 USING (answerID)
 GROUP BY   A.answerID

Here's the data the query returns:

http://wtomlinson.com/evalQRYs/qryData.jpg

Notice how it's indeed returning 5 rows - 1 per answer choice. But It's also 
just returning 1 question. There were 3 questions answered. I've played with it 
some and will keep trying. 

Any ideas?

Thanks much,
Will 


~|
ColdFusion MX7 and Flex 2 
Build sales  marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273179
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Need some SQL help!

I may have figured it out...

I used this for my group by. 

GROUP BY Q.questionID, A.answerID

Still needta test it more tho but my results are lookin better. 

Thanks Jochem!

Will

~|
ColdFusion MX7 and Flex 2 
Build sales  marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273182
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Need some SQL help!

Ok, I ALMOST got it! The results that are incorrect is just the number of 
answers per question. 

Here's what the query returns: 

http://wtomlinson.com/evalQRYs/qryData.jpg

Look at numAnswers. I completed two evaluations. But numAnswers should be the 
total count of each answer that was given. I obviously didn't answer twice for 
every answer as shown in the query results. 

I need it to count up the number of answers that were chosen, for each answer, 
for each question. 

Here's my query as it stands now:

ALMOST! 

SELECT
Q.questionID,
Q.questionsetID,
Q.question,
Q.answersetid,
ARS.answersetid,
ARS.answersetname,
A.answerID,
A.answersetID,
A.answername, 
A.answersortnum, 
A.answervalue,
EAR.evalID, 
EAR.answerID,
E.evalID,
E.InstructorID,
 
COUNT(EAR.answerID) AS numAnswers

FROM   tblquestions Q  
INNER JOIN   tblanswersets ARS
 USING (answerSetID)
 INNER JOIN   tblanswers A
 USING (answerSetID)  
 LEFT JOIN  tblevalanswerresults EAR
 USING (questionID)
 INNER JOIN tblEvaluations E ON
 E.evalID = EAR.evalID
WHERE  E.InstructorID = '1021338' 
 GROUP BY   Q.questionID, A.answerID

Thanks,
Will

 

~|
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:273191
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Need More SQL help - DRIVING ME NUTS!