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('FFFFE7'),
> 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#
> </cfquery><font 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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
