Is there a way to display a COUNT of these Inner Joined records? Right now, my .NET Datalist is listing all of the records for this team; however, I just want it to count the number of records and then let me display the most recent TIME (my field is “fldDate”) for the most recent CP reached.
"SELECT * FROM (tblEvents INNER JOIN (tblCategory INNER JOIN " & _
"tblRegistrationBasic ON [tblCategory].[CategoryID]=[tblRegistrationBasic].[CategoryID]) ON " & _
"[tblEvents].[EID]=[tblRegistrationBasic].[EventID]) INNER JOIN tblCPReached ON " & _
"[tblRegistrationBasic].[RID]=[tblCPReached].[Team] " & _
"WHERE tblEvents.EID=" & _
Request.QueryString("id") & " ORDER BY Chek DESC", objConn)
You’re no longer selecting that column – you said you only want to get the count and the maximum date. That’s what the query will give you. You can’t bind it to some interface designed to show something else.
Oh, so I can’t display items from the INNER Joined tables, while counting the number of records for each similar item and then order by the number collected?
actually, you can, but they all have to be within an aggregate function
if you want counts per team (as your last example suggested), you also need a GROUP BY clause
SELECT tblCPReached.Team AS 'Team Name'
, COUNT(*) AS 'CPs Collected'
FROM ((
tblEvents
INNER
JOIN tblRegistrationBasic
ON tblRegistrationBasic.EventID = tblEvents.EID
)
INNER
JOIN tblCategory
ON tblCategory.CategoryID = tblRegistrationBasic.CategoryID
)
INNER
JOIN tblCPReached
ON tblCPReached.Team = tblRegistrationBasic.RID
WHERE tblEvents.EID = Request.QueryString("id")
GROUP
BY tblCPReached.Team
ORDER
BY COUNT(*) DESC
The error doesn’t mean the query’s wrong. I don’t think you want to bind this to something designed to show individual objects based on a single table.
I just created a manual leaderboard. I’m going to edit the time and last CP for each team and have it sort that way. My race is this weekend. I’ll try to build one that automatically does it later. Here is a sample of what I’m doing