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
