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)
playerhistory_FixtureID, playerhistory_Goals, playerhistory_PlayerID
1, 4, 222
2, 2, 223
1, 1, 224
1, 4, 225
2, 1, 225
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:
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.
This should do it:
Hope this helps,Oleg
Oleg thanks a million for this....worked a treat.
answered Aug 30 '10 at 05:06 AM