Couple different ways to fix this. Best way would be to do the join and do
it in one query. Try something like this:
<cfquery name="GetTotalPtsOneWoman"
datasource="#datasource#">
SELECT
rm.ContestantID,
SUM(rm.AdjPoints) as totalpts,
SUM(rm.MoneyEarned) as totalmoney
FROM
resultsmanager rm JOIN ContestantManager cm
ON rm.contestantID = cm.contestantID
WHERE
cm.gender = '2'
GROUP BY
rm.contesetantID
ORDER BY
totalpts DESC
</cfquery>
If this works, I'd appreciate the bonus in small, unmarked bills ;-)
-----Original Message-----
From: Jay Patton [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 16, 2001 2:53 PM
To: CF-Talk
Subject: am i missing something?
Hello everyone,
I'm looking for help on this page.
i have the following query's runing. i have comments above each to help out
and below i explain more.
<!-- Query For Women ONLY -->
<cfquery name="GetWomen"
datasource="#datasource#"
dbtype="ODBC">
SELECT Gender, ContestantID
FROM ContestantManager
WHERE Gender = '2'
</cfquery>
<!-- Gets total points and money for the selected contestants -->
<cfquery name="GetTotalPts"
datasource="#datasource#"
dbtype="ODBC">
SELECT #GetWomen.ContestantID#, SUM (AdjPoints) as TOTALPTS, SUM
(MoneyEarned) as TOTALMONEY
FROM ResultsManager
GROUP BY #GetWomen.ContestantID#
ORDER BY SUM (AdjPoints) DESC
</cfquery>
<cfset Place = GetTotalPts.RecordCount>
<cfset count = 1>
<!-- Some html code -->
<!-- Then this is my output -->
<CFPARAM NAME="URL.StartRow" DEFAULT="1">
<cfoutput query="GetTotalPts" startrow=#URL.StartRow# maxrows=25>
<tr bgcolor="#IIf(GetTotalPts.CurrentRow Mod 2, DE('FFFFE7'),
DE('99CC99'))#">
<td width="6"><font face="verdana,arial,helvetica"
size="1"><center>#currentRow#</center></font></td>
<td width="251"><font face="Verdana, Arial, Helvetica, sans-serif"
size="1"><cfquery name="GetNames" datasource="#datasource#"
dbtype="ODBC">SELECT * FROM ContestantManager WHERE ContestantID =
#GetWomen.ContestantID# AND Gender =
'#GetWomen.Gender#'</cfquery>#GetNames.LastName#, #GetNames.FirstName# <font
size="1">(#GetNames.City#, #GetNames.State#)</font></font></td>
<td width="85"><font face="Verdana, Arial, Helvetica, sans-serif"
size="1">#GetTotalPts.TOTALPTS#</font></td>
<td width="103"><font face="Verdana, Arial, Helvetica, sans-serif"
size="1">$#GetTotalPts.TOTALMONEY#</font></td>
</tr>
</cfoutput>
This seems to grab the first lady in the db and returns her ONLY but it
totals all of the pts. ive been trying to get it to output just the lady's
in the db with there totals ect.... but the gender's and the results are in
two different tables does that create a problem? (im not very familiar with
joining tables but im more than willing to learn if that will fix my
problem.. but i need this done by today (BONUS and a RAISE :) any ideas?
your help is much much much appreciated.
Thanks ALOT,
Jay Patton
Web Design / Application Design
Web Pro USA
406.549.3337 ext. 203
1.888.5WEBPRO
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
