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
