Count with Inner Join

Hello,

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)

Thanks, Dan,

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 :slight_smile:

Rodney

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 :confused:

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
SteelSports 3

Thanks,
Rodney

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

Same error. Thanks for trying, though, guys.

Rodney

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 :slight_smile:

http://www.steelsports.net/RaceUpdates.aspx?id=309

Thanks again.