Count with Inner Join
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)
SELECT COUNT(*), MAX(fldDate)
I'm not a programmer (just reading books), so where in my existing code would I add that information?
Sorry to ask a stupid question, but thank you :)
Instead of SELECT *, which asks for all the columns of the tables.
and don't forget to remove the ORDER BY clause
Now I get the error:
DataBinding: 'System.Data.Common.DataRecordInternal' does not contain a property with the name 'RID'
"RID" is the first column in my "tblRegistrationBasic" table.
Not sure why it's not finding that column :/
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?
Team Name CPs Collected
Joe Blow 4
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'
ON tblRegistrationBasic.EventID = tblEvents.EID
ON tblCategory.CategoryID = tblRegistrationBasic.CategoryID
ON tblCPReached.Team = tblRegistrationBasic.RID
WHERE tblEvents.EID = Request.QueryString("id")
BY COUNT(*) DESC
Same error. Thanks for trying, though, guys.
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 :)