hello all,
i was wondering if someone might be able to help me with this. im new to CF and dont
totally understand list yet. (im not even sure if this is the rite way to go about
this but...) im using the following query to get each contestant that has been in two
or more different events. however its returning even the contestants that have done
the same event over and over for the year. it is only supposed to grab the ones that
compete in two or more DIFFERENT events. i think this output is possible using a list
but im not very familiar w/ lists yet. if someone has any ideas that would be great!
example out put (what it should be)
Contestant Name | Events | should he output?
Joe 2,2,2,2 NO
Bill 1,1,2,3,4 YES
Marry 2 NO
Kim 2,4, YES
==My query=
<cfquery name="GetTotalPts"
datasource="#datasource#"
dbtype="ODBC"
username="#username#"
password="#password#">
SELECT ResultsManager.ContestantID, ResultsManager.EventID,
ResultsManager.NotAllAround, ContestantManager.ContestantID,
ContestantManager.FirstName, ContestantManager.LastName, ContestantManager.Gender,
ContestantManager.City, ContestantManager.State, ContestantManager.Age, SUM
(ResultsManager.AdjPoints) as TOTALPTS
FROM ResultsManager INNER JOIN ContestantManager ON ResultsManager.ContestantID =
ContestantManager.ContestantID
GROUP BY ResultsManager.ContestantID, ResultsManager.EventID,
ContestantManager.ContestantID, ContestantManager.FirstName,
ContestantManager.LastName, ContestantManager.City, ContestantManager.State,
ContestantManager.Age, ContestantManager.Gender, ResultsManager.NotAllAround
HAVING COUNT (ResultsManager.EventID) >= 2 AND ContestantManager.Age > '51' AND
ContestantManager.Gender = '1' AND ResultsManager.NotAllAround = '0'
ORDER BY SUM (AdjPoints) DESC
</cfquery>
==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">#GetTotalPts.LastName#, #GetTotalPts.FirstName# <font
size="1">(#GetTotalPts.City#, #GetTotalPts.State#)</font></font></td>
<td width="85"><font face="Verdana, Arial, Helvetica, sans-serif"
size="1">#NumberFormat(GetTotalPts.TOTALPTS,"____.__")#</font></td>
</tr>
</cfoutput>
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
