RE: statement help...

2001-04-23 Thread Adkins, Randy 

Make sure that in the DB there are 12 for the exact length
and spelling of the URL.CircuitName

I had previous experience with the DB field containing
an extra space so the following are NOT the same:

URL.CircuitName = CircuitOne
DB.CircuitName= CircuitOne  



-Original Message-
From: Jay Patton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 23, 2001 11:32 AM
To: CF-Talk
Subject: statement help...


Why would this only select one record from the db?(there are about 12 it
should return but it only returns one (which is the first one)

cfquery name=getRodeos
 datasource=#datasource#
 dbtype=ODBC
 username=#username#
 password=#password#
SELECT *
FROM RodeoManager
WHERE Circuit = '#url.CircuitName#'
/cfquery


Thanks,

Jay Patton
Web Design / Application Design
Web Pro USA
p. 406.549.3337 ext. 203
p. 1.888.5WEBPRO ext. 203
e. [EMAIL PROTECTED]
url. www.webpro-usa.com
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: statement help...

2001-04-23 Thread Mike Sprague 

Try the following as your where clause:

WHERE trim(Circuit) = '#trim(url.CircuitName)#'

*** REPLY SEPARATOR  ***
On 4/23/2001 at 9:31 AM Jay Patton wrote:

|Why would this only select one record from the db?(there are about 12 it
|should return but it only returns one (which is the first one)
|
|cfquery name=getRodeos
| datasource=#datasource#
| dbtype=ODBC
| username=#username#
| password=#password#
|SELECT *
|FROM RodeoManager
|WHERE Circuit = '#url.CircuitName#'
|/cfquery
|
|
|Thanks,
|
|Jay Patton
|Web Design / Application Design
|Web Pro USA
|p. 406.549.3337 ext. 203
|p. 1.888.5WEBPRO ext. 203
|e. [EMAIL PROTECTED]
|url. www.webpro-usa.com
|
| |
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: statement help...

2001-04-23 Thread Jay Patton 

i tried this also, and it still didnt work
since then i have switched everything to be id specific now however, it
still only returns the first record w/ that id, now. any ideas this is
my query now

cfquery name=getRodeos
 datasource=#datasource#
 dbtype=ODBC
 username=#username#
 password=#password#
SELECT *
FROM RodeoManager
WHERE CircuitID = #url.CircuitID#
/cfquery

thanks,

Jay Patton
Web Design / Application Design
Web Pro USA
p. 406.549.3337 ext. 203
p. 1.888.5WEBPRO ext. 203
e. [EMAIL PROTECTED]
url. www.webpro-usa.com
- Original Message -
From: Mike Sprague [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, April 23, 2001 9:48 AM
Subject: Re: statement help...


 Try the following as your where clause:

 WHERE trim(Circuit) = '#trim(url.CircuitName)#'

 *** REPLY SEPARATOR  ***
 On 4/23/2001 at 9:31 AM Jay Patton wrote:

 |Why would this only select one record from the db?(there are about 12 it
 |should return but it only returns one (which is the first one)
 |
 |cfquery name=getRodeos
 | datasource=#datasource#
 | dbtype=ODBC
 | username=#username#
 | password=#password#
 |SELECT *
 |FROM RodeoManager
 |WHERE Circuit = '#url.CircuitName#'
 |/cfquery
 |
 |
 |Thanks,
 |
 |Jay Patton
 |Web Design / Application Design
 |Web Pro USA
 |p. 406.549.3337 ext. 203
 |p. 1.888.5WEBPRO ext. 203
 |e. [EMAIL PROTECTED]
 |url. www.webpro-usa.com
 |
 | |

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: statement help...

2001-04-23 Thread Diana Nichols 

Perhaps the problem lies in the outputwhat does that code look like?

*
Diana Nichols
Webmistress
http://www.lavenderthreads.com
770.434.7374

One man's magic is another man's engineering. ---Lazarus Long

-Original Message-
From: Jay Patton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 23, 2001 12:17 PM
To: CF-Talk
Subject: Re: statement help...


i tried this also, and it still didnt work
since then i have switched everything to be id specific now however, it
still only returns the first record w/ that id, now. any ideas this is
my query now

cfquery name=getRodeos
 datasource=#datasource#
 dbtype=ODBC
 username=#username#
 password=#password#
SELECT *
FROM RodeoManager
WHERE CircuitID = #url.CircuitID#
/cfquery

thanks,

Jay Patton
Web Design / Application Design
Web Pro USA
p. 406.549.3337 ext. 203
p. 1.888.5WEBPRO ext. 203
e. [EMAIL PROTECTED]
url. www.webpro-usa.com
- Original Message -
From: Mike Sprague [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, April 23, 2001 9:48 AM
Subject: Re: statement help...


 Try the following as your where clause:

 WHERE trim(Circuit) = '#trim(url.CircuitName)#'

 *** REPLY SEPARATOR  ***
 On 4/23/2001 at 9:31 AM Jay Patton wrote:

 |Why would this only select one record from the db?(there are about 12 it
 |should return but it only returns one (which is the first one)
 |
 |cfquery name=getRodeos
 | datasource=#datasource#
 | dbtype=ODBC
 | username=#username#
 | password=#password#
 |SELECT *
 |FROM RodeoManager
 |WHERE Circuit = '#url.CircuitName#'
 |/cfquery
 |
 |
 |Thanks,
 |
 |Jay Patton
 |Web Design / Application Design
 |Web Pro USA
 |p. 406.549.3337 ext. 203
 |p. 1.888.5WEBPRO ext. 203
 |e. [EMAIL PROTECTED]
 |url. www.webpro-usa.com
 |
 | |

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: statement help...

2001-04-23 Thread Dave f 

How do you know there are 12-ish if you only see 1??

Are you saying that  getRodeos.RecordCount = 1, or have you maybe forgotten
to specify queryname in cfoutput loop?

Dave

- Original Message -
From: Jay Patton [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, April 23, 2001 12:16 PM
Subject: Re: statement help...


 i tried this also, and it still didnt work
 since then i have switched everything to be id specific now however, it
 still only returns the first record w/ that id, now. any ideas this is
 my query now

 cfquery name=getRodeos
  datasource=#datasource#
  dbtype=ODBC
  username=#username#
  password=#password#
 SELECT *
 FROM RodeoManager
 WHERE CircuitID = #url.CircuitID#
 /cfquery

 thanks,

 Jay Patton
 Web Design / Application Design
 Web Pro USA
 p. 406.549.3337 ext. 203
 p. 1.888.5WEBPRO ext. 203
 e. [EMAIL PROTECTED]
 url. www.webpro-usa.com
 - Original Message -
 From: Mike Sprague [EMAIL PROTECTED]
 To: CF-Talk [EMAIL PROTECTED]
 Sent: Monday, April 23, 2001 9:48 AM
 Subject: Re: statement help...


  Try the following as your where clause:
 
  WHERE trim(Circuit) = '#trim(url.CircuitName)#'
 
  *** REPLY SEPARATOR  ***
  On 4/23/2001 at 9:31 AM Jay Patton wrote:
 
  |Why would this only select one record from the db?(there are about 12
it
  |should return but it only returns one (which is the first one)
  |
  |cfquery name=getRodeos
  | datasource=#datasource#
  | dbtype=ODBC
  | username=#username#
  | password=#password#
  |SELECT *
  |FROM RodeoManager
  |WHERE Circuit = '#url.CircuitName#'
  |/cfquery
  |
  |
  |Thanks,
  |
  |Jay Patton
  |Web Design / Application Design
  |Web Pro USA
  |p. 406.549.3337 ext. 203
  |p. 1.888.5WEBPRO ext. 203
  |e. [EMAIL PROTECTED]
  |url. www.webpro-usa.com
  |
  | |
 

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: statement help...

2001-04-23 Thread Andy Ewings 

Have you tried running this code directly at the db?...i.e. if SQL run in
query analyser.  One thing you could try is put trim statements around the
fied (or LTRIM and RTRIM if you are using SQL). i.e:

SELECT * FROM RodeoManager WHERE TRIM(CircuitID) = #TRIM(url.CircuitID)# 

OR:

SELECT * FROM RodeoManager WHERE LTRIM(RTRIM(CircuitID)) =
#TRIM(url.CircuitID)# 

-- 
Andrew Ewings
Project Manager
Thoughtbubble Ltd 
http://www.thoughtbubble.net 
-- 
United Kingdom 
http://www.thoughtbubble.co.uk/ 
Tel: +44 (0) 20 7387 8890 
-- 
New Zealand 
http://www.thoughtbubble.co.nz/ 
Tel: +64 (0) 9 488 9131
-- 
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890. 



-Original Message-
From: Dave f [mailto:[EMAIL PROTECTED]]
Sent: 23 April 2001 18:07
To: CF-Talk
Subject: Re: statement help...


How do you know there are 12-ish if you only see 1??

Are you saying that  getRodeos.RecordCount = 1, or have you maybe forgotten
to specify queryname in cfoutput loop?

Dave

- Original Message -
From: Jay Patton [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, April 23, 2001 12:16 PM
Subject: Re: statement help...


 i tried this also, and it still didnt work
 since then i have switched everything to be id specific now however, it
 still only returns the first record w/ that id, now. any ideas this is
 my query now

 cfquery name=getRodeos
  datasource=#datasource#
  dbtype=ODBC
  username=#username#
  password=#password#
 SELECT *
 FROM RodeoManager
 WHERE CircuitID = #url.CircuitID#
 /cfquery

 thanks,

 Jay Patton
 Web Design / Application Design
 Web Pro USA
 p. 406.549.3337 ext. 203
 p. 1.888.5WEBPRO ext. 203
 e. [EMAIL PROTECTED]
 url. www.webpro-usa.com
 - Original Message -
 From: Mike Sprague [EMAIL PROTECTED]
 To: CF-Talk [EMAIL PROTECTED]
 Sent: Monday, April 23, 2001 9:48 AM
 Subject: Re: statement help...


  Try the following as your where clause:
 
  WHERE trim(Circuit) = '#trim(url.CircuitName)#'
 
  *** REPLY SEPARATOR  ***
  On 4/23/2001 at 9:31 AM Jay Patton wrote:
 
  |Why would this only select one record from the db?(there are about 12
it
  |should return but it only returns one (which is the first one)
  |
  |cfquery name=getRodeos
  | datasource=#datasource#
  | dbtype=ODBC
  | username=#username#
  | password=#password#
  |SELECT *
  |FROM RodeoManager
  |WHERE Circuit = '#url.CircuitName#'
  |/cfquery
  |
  |
  |Thanks,
  |
  |Jay Patton
  |Web Design / Application Design
  |Web Pro USA
  |p. 406.549.3337 ext. 203
  |p. 1.888.5WEBPRO ext. 203
  |e. [EMAIL PROTECTED]
  |url. www.webpro-usa.com
  |
  | |
 

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: statement help...

2001-04-23 Thread Christopher Olive, CIO 

are you only outputting one?  as in, do you have QUERY specified in the
CFOUTPUT tag?  or does your debug show only one returned?

chris olive, cio
cresco technologies
[EMAIL PROTECTED]
http://www.crescotech.com



-Original Message-
From: Jay Patton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 23, 2001 12:17 PM
To: CF-Talk
Subject: Re: statement help...


i tried this also, and it still didnt work
since then i have switched everything to be id specific now however, it
still only returns the first record w/ that id, now. any ideas this is
my query now

cfquery name=getRodeos
 datasource=#datasource#
 dbtype=ODBC
 username=#username#
 password=#password#
SELECT *
FROM RodeoManager
WHERE CircuitID = #url.CircuitID#
/cfquery

thanks,

Jay Patton
Web Design / Application Design
Web Pro USA
p. 406.549.3337 ext. 203
p. 1.888.5WEBPRO ext. 203
e. [EMAIL PROTECTED]
url. www.webpro-usa.com
- Original Message -
From: Mike Sprague [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, April 23, 2001 9:48 AM
Subject: Re: statement help...


 Try the following as your where clause:

 WHERE trim(Circuit) = '#trim(url.CircuitName)#'

 *** REPLY SEPARATOR  ***
 On 4/23/2001 at 9:31 AM Jay Patton wrote:

 |Why would this only select one record from the db?(there are about 12 it
 |should return but it only returns one (which is the first one)
 |
 |cfquery name=getRodeos
 | datasource=#datasource#
 | dbtype=ODBC
 | username=#username#
 | password=#password#
 |SELECT *
 |FROM RodeoManager
 |WHERE Circuit = '#url.CircuitName#'
 |/cfquery
 |
 |
 |Thanks,
 |
 |Jay Patton
 |Web Design / Application Design
 |Web Pro USA
 |p. 406.549.3337 ext. 203
 |p. 1.888.5WEBPRO ext. 203
 |e. [EMAIL PROTECTED]
 |url. www.webpro-usa.com
 |
 | |

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: statement help...

2001-04-23 Thread Jay Patton 

I know there is more than one because i am looking in that specific table
and see 12 (at least) records that should be returned.
!--- Query One ---
cfquery name=getRodeos
 datasource=#datasource#
 dbtype=ODBC
 username=#username#
 password=#password#
SELECT *
FROM RodeoManager
WHERE CircuitID = #url.CircuitID#
/cfquery
!--- Query Two ---
cfquery name=resultsP
 datasource=#datasource#
 dbtype=ODBC
 username=#username#
 password=#password#
SELECT SUM(AdjPoints) as totalPoints, ContestantID
FROM ResultsManager
WHERE RodeoID = #getRodeos.RodeoID#
GROUP BY ContestantID
ORDER BY SUM(AdjPoints) DESC
/cfquery

!--- output ---
cfoutput query=resultsP
cfif resultsP.totalPoints GT 0.00
  tr bgcolor=#IIf(resultsP.CurrentRow  Mod 2, DE('E7'),
DE('99CC99'))#
td width=12%font face=Verdana, Arial, Helvetica, sans-serif
size=1#currentRow#/font/td
td width=69%
 cfquery name=contestants
 datasource=#datasource#
 dbtype=ODBC
 username=#username#
 password=#password#
SELECT *
FROM ContestantManager
WHERE ContestantID = #resultsP.ContestantID#
/cfqueryfont face=Verdana, Arial, Helvetica, sans-serif
size=1#contestants.LastName#, #contestants.FirstName#
(#contestants.City#, #contestants.State#)/font/td
td width=19%font face=Verdana, Arial, Helvetica, sans-serif
size=1#totalPoints#/font/td
  /tr
  /cfif
/cfoutput

Thanks,

Jay Patton
Web Design / Application Design
Web Pro USA
p. 406.549.3337 ext. 203
p. 1.888.5WEBPRO ext. 203
e. [EMAIL PROTECTED]
url. www.webpro-usa.com
- Original Message -
From: Dave f [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, April 23, 2001 11:06 AM
Subject: Re: statement help...


 How do you know there are 12-ish if you only see 1??

 Are you saying that  getRodeos.RecordCount = 1, or have you maybe
forgotten
 to specify queryname in cfoutput loop?

 Dave

 - Original Message -
 From: Jay Patton [EMAIL PROTECTED]
 To: CF-Talk [EMAIL PROTECTED]
 Sent: Monday, April 23, 2001 12:16 PM
 Subject: Re: statement help...


  i tried this also, and it still didnt work
  since then i have switched everything to be id specific now however, it
  still only returns the first record w/ that id, now. any ideas this
is
  my query now
 
  cfquery name=getRodeos
   datasource=#datasource#
   dbtype=ODBC
   username=#username#
   password=#password#
  SELECT *
  FROM RodeoManager
  WHERE CircuitID = #url.CircuitID#
  /cfquery
 
  thanks,
 
  Jay Patton
  Web Design / Application Design
  Web Pro USA
  p. 406.549.3337 ext. 203
  p. 1.888.5WEBPRO ext. 203
  e. [EMAIL PROTECTED]
  url. www.webpro-usa.com
  - Original Message -
  From: Mike Sprague [EMAIL PROTECTED]
  To: CF-Talk [EMAIL PROTECTED]
  Sent: Monday, April 23, 2001 9:48 AM
  Subject: Re: statement help...
 
 
   Try the following as your where clause:
  
   WHERE trim(Circuit) = '#trim(url.CircuitName)#'
  
   *** REPLY SEPARATOR  ***
   On 4/23/2001 at 9:31 AM Jay Patton wrote:
  
   |Why would this only select one record from the db?(there are about 12
 it
   |should return but it only returns one (which is the first one)
   |
   |cfquery name=getRodeos
   | datasource=#datasource#
   | dbtype=ODBC
   | username=#username#
   | password=#password#
   |SELECT *
   |FROM RodeoManager
   |WHERE Circuit = '#url.CircuitName#'
   |/cfquery
   |
   |
   |Thanks,
   |
   |Jay Patton
   |Web Design / Application Design
   |Web Pro USA
   |p. 406.549.3337 ext. 203
   |p. 1.888.5WEBPRO ext. 203
   |e. [EMAIL PROTECTED]
   |url. www.webpro-usa.com
   |
   | |
  
 

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: statement help...

2001-04-23 Thread Dave f 

Not too sure of format off the top of my head, but you it looks like you
need something like

cfquery name=resultsP.
SELECT SUM(AdjPoints) as totalPoints, ContestantID
 FROM ResultsManager
 WHERE RodeoID IN
(SELECT RodeoID FROM RodeoManager
WHERE CircuitID = #url.CircuitID# )

hope this at least points you in right direction.

- Original Message -
From: Jay Patton [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, April 23, 2001 1:45 PM
Subject: Re: statement help...


 I know there is more than one because i am looking in that specific table
 and see 12 (at least) records that should be returned.
 !--- Query One ---
 cfquery name=getRodeos
  datasource=#datasource#
  dbtype=ODBC
  username=#username#
  password=#password#
 SELECT *
 FROM RodeoManager
 WHERE CircuitID = #url.CircuitID#
 /cfquery
 !--- Query Two ---
 cfquery name=resultsP
  datasource=#datasource#
  dbtype=ODBC
  username=#username#
  password=#password#
 SELECT SUM(AdjPoints) as totalPoints, ContestantID
 FROM ResultsManager
 WHERE RodeoID = #getRodeos.RodeoID#
 GROUP BY ContestantID
 ORDER BY SUM(AdjPoints) DESC
 /cfquery

 !--- output ---
 cfoutput query=resultsP
 cfif resultsP.totalPoints GT 0.00
   tr bgcolor=#IIf(resultsP.CurrentRow  Mod 2, DE('E7'),
 DE('99CC99'))#
 td width=12%font face=Verdana, Arial, Helvetica, sans-serif
 size=1#currentRow#/font/td
 td width=69%
  cfquery name=contestants
  datasource=#datasource#
  dbtype=ODBC
  username=#username#
  password=#password#
 SELECT *
 FROM ContestantManager
 WHERE ContestantID = #resultsP.ContestantID#
 /cfqueryfont face=Verdana, Arial, Helvetica, sans-serif
 size=1#contestants.LastName#, #contestants.FirstName#
 (#contestants.City#, #contestants.State#)/font/td
 td width=19%font face=Verdana, Arial, Helvetica, sans-serif
 size=1#totalPoints#/font/td
   /tr
   /cfif
 /cfoutput

 Thanks,

 Jay Patton
 Web Design / Application Design
 Web Pro USA
 p. 406.549.3337 ext. 203
 p. 1.888.5WEBPRO ext. 203
 e. [EMAIL PROTECTED]
 url. www.webpro-usa.com
 - Original Message -
 From: Dave f [EMAIL PROTECTED]
 To: CF-Talk [EMAIL PROTECTED]
 Sent: Monday, April 23, 2001 11:06 AM
 Subject: Re: statement help...


  How do you know there are 12-ish if you only see 1??
 
  Are you saying that  getRodeos.RecordCount = 1, or have you maybe
 forgotten
  to specify queryname in cfoutput loop?
 
  Dave
 
  - Original Message -
  From: Jay Patton [EMAIL PROTECTED]
  To: CF-Talk [EMAIL PROTECTED]
  Sent: Monday, April 23, 2001 12:16 PM
  Subject: Re: statement help...
 
 
   i tried this also, and it still didnt work
   since then i have switched everything to be id specific now however,
it
   still only returns the first record w/ that id, now. any ideas
this
 is
   my query now
  
   cfquery name=getRodeos
datasource=#datasource#
dbtype=ODBC
username=#username#
password=#password#
   SELECT *
   FROM RodeoManager
   WHERE CircuitID = #url.CircuitID#
   /cfquery
  
   thanks,
  
   Jay Patton
   Web Design / Application Design
   Web Pro USA
   p. 406.549.3337 ext. 203
   p. 1.888.5WEBPRO ext. 203
   e. [EMAIL PROTECTED]
   url. www.webpro-usa.com
   - Original Message -
   From: Mike Sprague [EMAIL PROTECTED]
   To: CF-Talk [EMAIL PROTECTED]
   Sent: Monday, April 23, 2001 9:48 AM
   Subject: Re: statement help...
  
  
Try the following as your where clause:
   
WHERE trim(Circuit) = '#trim(url.CircuitName)#'
   
*** REPLY SEPARATOR  ***
On 4/23/2001 at 9:31 AM Jay Patton wrote:
   
|Why would this only select one record from the db?(there are about
12
  it
|should return but it only returns one (which is the first one)
|
|cfquery name=getRodeos
| datasource=#datasource#
| dbtype=ODBC
| username=#username#
| password=#password#
|SELECT *
|FROM RodeoManager
|WHERE Circuit = '#url.CircuitName#'
|/cfquery
|
|
|Thanks,
|
|Jay Patton
|Web Design / Application Design
|Web Pro USA
|p. 406.549.3337 ext. 203
|p. 1.888.5WEBPRO ext. 203
|e. [EMAIL PROTECTED]
|url. www.webpro-usa.com
|
| |
   
  
 

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: Statement Help!!

2001-03-28 Thread Hayes, David 

What results are you getting? 

Take a look at the DISTINCT COUNT function, instead of the COUNT function.
This should take care of the same eventID in different rodeos.

-Original Message-
From: Jay Patton [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 28, 2001 2:09 PM
To: CF-Talk
Subject: Statement Help!!


what i am trying to accomplish here is to show ONLY those contestants that
have 2 or more DIFFERANT EventID's in the ResultsManager (table in my SQL7
DB) (example: if they do the same event in every rodeo for the year they
should NOT show up, but if they do 2 or more they should) the following is
my query but something isnt working write and i cant seem to figure it out.
if anyone could help that would be great!

my query:

cfquery name="GetTotalPts"
 datasource="#datasource#"
 dbtype="ODBC"
 username="#username#"
 password="#password#"
SELECT ResultsManager.ContestantID, ResultsManager.EventID,
ContestantManager.FirstName, ContestantManager.LastName,
ContestantManager.City, ContestantManager.State, ContestantManager.Age, SUM
(ResultsManager.AdjPoints) as TOTALPTS,
COUNT (EventID)
FROM ResultsManager INNER JOIN ContestantManager ON
ResultsManager.ContestantID = ContestantManager.ContestantID
WHERE ContestantManager.Age  '51' AND ContestantManager.Gender = '1' AND
ResultsManager.NotAllAround = '0'
GROUP BY ResultsManager.ContestantID, ResultsManager.EventID,
ContestantManager.FirstName, ContestantManager.LastName,
ContestantManager.City, ContestantManager.State, ContestantManager.Age
HAVING COUNT (EventID) = 2
ORDER BY SUM (AdjPoints) DESC
/cfquery

Thanks in advance,

Jay Patton
Web Design / Application Design
Web Pro USA
p. 406.549.3337 ext. 203
p. 1.888.5WEBPRO ext. 203
e. [EMAIL PROTECTED]
url. www.webpro-usa.com
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: Statement Help!!

2001-03-28 Thread Bob Silverberg 

What happens if you change the COUNT(EventID) to COUNT(DISTINCT EventID)?

Bob

-Original Message-
From: Jay Patton [mailto:[EMAIL PROTECTED]]
Sent: March 28, 2001 3:09 PM
To: CF-Talk
Subject: Statement Help!!


what i am trying to accomplish here is to show ONLY those contestants that
have 2 or more DIFFERANT EventID's in the ResultsManager (table in my SQL7
DB) (example: if they do the same event in every rodeo for the year they
should NOT show up, but if they do 2 or more they should) the following is
my query but something isnt working write and i cant seem to figure it out.
if anyone could help that would be great!

my query:

cfquery name="GetTotalPts"
 datasource="#datasource#"
 dbtype="ODBC"
 username="#username#"
 password="#password#"
SELECT ResultsManager.ContestantID, ResultsManager.EventID,
ContestantManager.FirstName, ContestantManager.LastName,
ContestantManager.City, ContestantManager.State, ContestantManager.Age, SUM
(ResultsManager.AdjPoints) as TOTALPTS,
COUNT (EventID)
FROM ResultsManager INNER JOIN ContestantManager ON
ResultsManager.ContestantID = ContestantManager.ContestantID
WHERE ContestantManager.Age  '51' AND ContestantManager.Gender = '1' AND
ResultsManager.NotAllAround = '0'
GROUP BY ResultsManager.ContestantID, ResultsManager.EventID,
ContestantManager.FirstName, ContestantManager.LastName,
ContestantManager.City, ContestantManager.State, ContestantManager.Age
HAVING COUNT (EventID) = 2
ORDER BY SUM (AdjPoints) DESC
/cfquery

Thanks in advance,

Jay Patton
Web Design / Application Design
Web Pro USA
p. 406.549.3337 ext. 203
p. 1.888.5WEBPRO ext. 203
e. [EMAIL PROTECTED]
url. www.webpro-usa.com
~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: Statement Help!!

2001-03-28 Thread Jay Patton 

i tried that and it doesnt show any results at all

Jay Patton
Web Design / Application Design
Web Pro USA
p. 406.549.3337 ext. 203
p. 1.888.5WEBPRO ext. 203
e. [EMAIL PROTECTED]
url. www.webpro-usa.com
- Original Message -
From: "Bob Silverberg" [EMAIL PROTECTED]
To: "CF-Talk" [EMAIL PROTECTED]
Sent: Wednesday, March 28, 2001 1:47 PM
Subject: RE: Statement Help!!


 What happens if you change the COUNT(EventID) to COUNT(DISTINCT EventID)?

 Bob

 -Original Message-
 From: Jay Patton [mailto:[EMAIL PROTECTED]]
 Sent: March 28, 2001 3:09 PM
 To: CF-Talk
 Subject: Statement Help!!


 what i am trying to accomplish here is to show ONLY those contestants that
 have 2 or more DIFFERANT EventID's in the ResultsManager (table in my SQL7
 DB) (example: if they do the same event in every rodeo for the year they
 should NOT show up, but if they do 2 or more they should) the following is
 my query but something isnt working write and i cant seem to figure it
out.
 if anyone could help that would be great!

 my query:

 cfquery name="GetTotalPts"
  datasource="#datasource#"
  dbtype="ODBC"
  username="#username#"
  password="#password#"
 SELECT ResultsManager.ContestantID, ResultsManager.EventID,
 ContestantManager.FirstName, ContestantManager.LastName,
 ContestantManager.City, ContestantManager.State, ContestantManager.Age,
SUM
 (ResultsManager.AdjPoints) as TOTALPTS,
 COUNT (EventID)
 FROM ResultsManager INNER JOIN ContestantManager ON
 ResultsManager.ContestantID = ContestantManager.ContestantID
 WHERE ContestantManager.Age  '51' AND ContestantManager.Gender = '1' AND
 ResultsManager.NotAllAround = '0'
 GROUP BY ResultsManager.ContestantID, ResultsManager.EventID,
 ContestantManager.FirstName, ContestantManager.LastName,
 ContestantManager.City, ContestantManager.State, ContestantManager.Age
 HAVING COUNT (EventID) = 2
 ORDER BY SUM (AdjPoints) DESC
 /cfquery

 Thanks in advance,

 Jay Patton
 Web Design / Application Design
 Web Pro USA
 p. 406.549.3337 ext. 203
 p. 1.888.5WEBPRO ext. 203
 e. [EMAIL PROTECTED]
 url. www.webpro-usa.com

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: Statement Help!!

2001-03-28 Thread Jay Patton 

would it be wise to try a sub query with this? or no? does anyone have any
other ideas or suggestions?
Thanks much,

Jay Patton
Web Design / Application Design
Web Pro USA
p. 406.549.3337 ext. 203
p. 1.888.5WEBPRO ext. 203
e. [EMAIL PROTECTED]
url. www.webpro-usa.com
- Original Message -
From: "Bob Silverberg" [EMAIL PROTECTED]
To: "CF-Talk" [EMAIL PROTECTED]
Sent: Wednesday, March 28, 2001 1:47 PM
Subject: RE: Statement Help!!


 What happens if you change the COUNT(EventID) to COUNT(DISTINCT EventID)?

 Bob

 -Original Message-
 From: Jay Patton [mailto:[EMAIL PROTECTED]]
 Sent: March 28, 2001 3:09 PM
 To: CF-Talk
 Subject: Statement Help!!


 what i am trying to accomplish here is to show ONLY those contestants that
 have 2 or more DIFFERANT EventID's in the ResultsManager (table in my SQL7
 DB) (example: if they do the same event in every rodeo for the year they
 should NOT show up, but if they do 2 or more they should) the following is
 my query but something isnt working write and i cant seem to figure it
out.
 if anyone could help that would be great!

 my query:

 cfquery name="GetTotalPts"
  datasource="#datasource#"
  dbtype="ODBC"
  username="#username#"
  password="#password#"
 SELECT ResultsManager.ContestantID, ResultsManager.EventID,
 ContestantManager.FirstName, ContestantManager.LastName,
 ContestantManager.City, ContestantManager.State, ContestantManager.Age,
SUM
 (ResultsManager.AdjPoints) as TOTALPTS,
 COUNT (EventID)
 FROM ResultsManager INNER JOIN ContestantManager ON
 ResultsManager.ContestantID = ContestantManager.ContestantID
 WHERE ContestantManager.Age  '51' AND ContestantManager.Gender = '1' AND
 ResultsManager.NotAllAround = '0'
 GROUP BY ResultsManager.ContestantID, ResultsManager.EventID,
 ContestantManager.FirstName, ContestantManager.LastName,
 ContestantManager.City, ContestantManager.State, ContestantManager.Age
 HAVING COUNT (EventID) = 2
 ORDER BY SUM (AdjPoints) DESC
 /cfquery

 Thanks in advance,

 Jay Patton
 Web Design / Application Design
 Web Pro USA
 p. 406.549.3337 ext. 203
 p. 1.888.5WEBPRO ext. 203
 e. [EMAIL PROTECTED]
 url. www.webpro-usa.com

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: Statement Help!!

2001-03-28 Thread Bob Silverberg 

That was going to be my next suggestion (sort of), although I must admit I'm
a bit surprised that switching to COUNT(DISTINCT EventID) didn't work.  When
you say that it "doesn't show any results at all" - do you mean that it
returns 0 records, or that you get an error message?  Are you running this
through CF, or using Query Analyzer (QA).  You'll probably find it easier to
write and test complex SQL statements if you run them through QA (that way
you can eliminate any possible problems with CF).

If it returns no records is it possible that that is because there _are_ no
records that satisfy the criteria?  Did you change both references to
COUNT(EventID)?  (there was one in the SELECT and one in the HAVING).

If none of the above helps, I'd suggest using a virtual table in a join.  I
thought about using a subselect, but I think a virtual table would be
better.

Let me know if none of the above helps and I can work on a query using a
virtual table for you.

Bob

-Original Message-
From: Jay Patton [mailto:[EMAIL PROTECTED]]
Sent: March 28, 2001 5:11 PM
To: CF-Talk
Subject: Re: Statement Help!!


would it be wise to try a sub query with this? or no? does anyone have any
other ideas or suggestions?
Thanks much,

Jay Patton
Web Design / Application Design
Web Pro USA
p. 406.549.3337 ext. 203
p. 1.888.5WEBPRO ext. 203
e. [EMAIL PROTECTED]
url. www.webpro-usa.com
- Original Message -
From: "Bob Silverberg" [EMAIL PROTECTED]
To: "CF-Talk" [EMAIL PROTECTED]
Sent: Wednesday, March 28, 2001 1:47 PM
Subject: RE: Statement Help!!


 What happens if you change the COUNT(EventID) to COUNT(DISTINCT EventID)?

 Bob

 -Original Message-
 From: Jay Patton [mailto:[EMAIL PROTECTED]]
 Sent: March 28, 2001 3:09 PM
 To: CF-Talk
 Subject: Statement Help!!


 what i am trying to accomplish here is to show ONLY those contestants that
 have 2 or more DIFFERANT EventID's in the ResultsManager (table in my SQL7
 DB) (example: if they do the same event in every rodeo for the year they
 should NOT show up, but if they do 2 or more they should) the following is
 my query but something isnt working write and i cant seem to figure it
out.
 if anyone could help that would be great!

 my query:

 cfquery name="GetTotalPts"
  datasource="#datasource#"
  dbtype="ODBC"
  username="#username#"
  password="#password#"
 SELECT ResultsManager.ContestantID, ResultsManager.EventID,
 ContestantManager.FirstName, ContestantManager.LastName,
 ContestantManager.City, ContestantManager.State, ContestantManager.Age,
SUM
 (ResultsManager.AdjPoints) as TOTALPTS,
 COUNT (EventID)
 FROM ResultsManager INNER JOIN ContestantManager ON
 ResultsManager.ContestantID = ContestantManager.ContestantID
 WHERE ContestantManager.Age  '51' AND ContestantManager.Gender = '1' AND
 ResultsManager.NotAllAround = '0'
 GROUP BY ResultsManager.ContestantID, ResultsManager.EventID,
 ContestantManager.FirstName, ContestantManager.LastName,
 ContestantManager.City, ContestantManager.State, ContestantManager.Age
 HAVING COUNT (EventID) = 2
 ORDER BY SUM (AdjPoints) DESC
 /cfquery

 Thanks in advance,

 Jay Patton
 Web Design / Application Design
 Web Pro USA
 p. 406.549.3337 ext. 203
 p. 1.888.5WEBPRO ext. 203
 e. [EMAIL PROTECTED]
 url. www.webpro-usa.com

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists