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

[ga_white]

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.

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

[chazzy]

the problem is that you're using all inner joins

[ga_white]

Thanks! I think I have it now ...