SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Whitehouse, TX
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.


    Code:
    "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)
    Rodney Skyles
    SteelSports.net, LLC

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    SELECT COUNT(*), MAX(fldDate)

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Whitehouse, TX
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Rodney
    Rodney Skyles
    SteelSports.net, LLC

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Instead of SELECT *, which asks for all the columns of the tables.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    and don't forget to remove the ORDER BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Whitehouse, TX
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 :/
    Rodney Skyles
    SteelSports.net, LLC

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Whitehouse, TX
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Rodney Skyles
    SteelSports.net, LLC

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Whitehouse, TX
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Same error. Thanks for trying, though, guys.

    Rodney
    Rodney Skyles
    SteelSports.net, LLC

  11. #11
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Whitehouse, TX
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


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

    Thanks again.
    Rodney Skyles
    SteelSports.net, LLC


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •