www.webdeveloper.com
Results 1 to 3 of 3

Thread: MSSQL Help! Just can't get this to work!

  1. #1
    Join Date
    May 2006
    Posts
    15

    MSSQL Help! Just can't get this to work!

    Hi everyone,

    I'm using MSSQL 2005 and the following in my existing query.

    The problem is that when there is no team record in Bonus table I don't end up with any Total Average, even with data in the others. When there is a team record in the Bonus table, the TotalAverage works great.

    Anybody spot the problem?


    Many thanks,

    G.


    Code:
    SELECT TOP (10) TT.TeamID, TT.TeamName, TT.Region, ROUND((DisT.TotalDistance + BT.TotalBonus) / PRTT.TotalParticipants, 1) AS TotalAverage 
    
    FROM  tblTeams AS TT INNER JOIN
             (SELECT TeamID, COUNT(*) AS TotalParticipants
             FROM tblParticipants AS tblParticipants_1
             GROUP BY TeamID) AS PRTT ON TT.TeamID = PRTT.TeamID INNER JOIN
             
             (SELECT TeamID, ISNULL(SUM(Distance), 0) AS TotalDistance
              FROM tblRBCdata
              GROUP BY TeamID) AS DisT ON TT.TeamID = DisT.TeamID INNER JOIN
    
             (SELECT TeamID, ISNULL(SUM(Bonus), 0) AS TotalBonus
                                FROM tblRBC_Comments
                                WHERE (Show = 1)
                                GROUP BY TeamID) AS BT ON TT.TeamID = BT.TeamID
    
    ORDER BY TotalAverage DESC

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    the problem is that you're using all inner joins

  3. #3
    Join Date
    May 2006
    Posts
    15
    Thanks! I think I have it now ...

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles