question

Hi all, I would really appreciate your help with this one. Table Name: Y2_Fixture (Relevant Fields: Fixture_ID) Table Name: Y2_Player (Relevant Fields: Player_ID, Player_Type) Table Name: Y2_PlayerHistory (Relevant Fields: playerhistory_FixtureID, playerhistory_Goals, playerhistory_PlayerID) Data: Y2_Fixture Fixture_ID 1 2 Data: Y2_PlayerHistory playerhistory_FixtureID, playerhistory_Goals, playerhistory_PlayerID 1, 4, 222 2, 2, 223 1, 1, 224 1, 4, 225 2, 1, 225 Data: Y2_Player player_ID, player_Type 222, C 223, C 224, C 225, O 226, O I am trying to arrange, by fixture, SUM of goals. There needs to be two SUMs because goals need to be counted seperately for player types 'C' and 'O' in the Y2_Player table ('C' and 'O' determine whether the player plays for the club or is a member of the opposition). Here's my SQL so far: SELECT fixture_ID, SUM(CB.ClubGoals), SUM(OP.OppositionGoals) FROM y2_fixture LEFT OUTER JOIN (SELECT playerhistory_FixtureID, playerhistory_Goals AS ClubGoals FROM Y2_PlayerHistory INNER JOIN Y2_Player ON player_ID = playerhistory_PlayerID WHERE player_Type = 'O') AS CB ON Y2_Fixture.fixture_ID = CB.playerhistory_FixtureID LEFT OUTER JOIN (SELECT playerhistory_FixtureID, Playerhistory_Goals AS OppositionGoals FROM dbo.Y2_PlayerHistory INNER JOIN Y2_Player ON player_ID = playerhistory_PlayerID WHERE player_Type = 'C') AS OP ON Y2_Fixture.fixture_ID = OP.playerhistory_FixtureID GROUP BY Y2_Fixture.fixture_ID The problem is that this gives me: fixture_ID, ClubGoals, OppositionGoals 1, 5, 8 2, 2, 2 For some reason, some of the scores are doubled by the query. I have found that by messing with the query the effect varies, sometimes scores on one on only the Clubside are doubled, sometimes only the OppositionGoals figure. I've even had it double the score on the ClubGoals on one record and so the same to OppostionGoals figure on the next records, leaving Clubgoals with the correct score. I have tried splitting up the query and running sections of it individually but everything seems fine. I know that I could achieve what I need by using multiple views but I am hoping to reduce the numbers of views used in the DB. So to summarise, I am trying to join two different queries from the same table back to a master fixture table. Sorry if the above is long winded but I thought it best to give you all the info I could. I'm using MS SQL 2008. Ladies and gents - any help you can offer would be greatly appreciated. Will