SQL query problem using Group By

[smurfsdabomb]

"Select Username, SUM(Score) FROM PlayerUpdate INNER JOIN Table1 ON Table1.PlayerID = Table2.PlayerID JOIN Table3 ON Table1.LeagueID = Table3.LeagueID WHERE Table1.LeagueID = " & Request.QueryString("titleleague") & " group by Table1.Username"

 

 

 

When I remove the group by and Sum part I get each of the results repeated twice presumably because of the table overlap but if I use Distinct Username I get the correct result and even if I use distinct within the SUM(distinct score) I get the correct result (though obviously a score of the same value would be discarded), but when I do the query as above I get a score of 28 instead of 14 because its taking the Score result twice, how do I ensure it is only summing it once?

 

Thanks!

 

[gnawz]

You could be missing an AND before the second JOIN and should it be JOIN or INNER JOIN?

[smurfsdabomb]

My bad, that should be Inner Join. Must have got deleted with the copy/paste over.

 

Any ideas whats going wrong? It might be something with the way I'm joining tables, if helps any this is the actual query without psuedonames and the relevant table data types.

 

SQLQuery = "Select FantasyTeamAssignment.Username, SUM(PlayerUpdate.CurrentScore) FROM PlayerUpdate INNER JOIN FantasyTeamAssignment ON FantasyTeamAssignment.PlayerID = PlayerUpdate.PlayerID JOIN LeagueSub ON FantasyTeamAssignment.LeagueID = LeagueSub.LeagueID WHERE FantasyTeamAssignment.LeagueID = " & Request.QueryString("titleleague") & " group by FantasyTeamAssignment.Username"

 

 

Tables

 

FantasyTeamAssignment - LeagueID (int), Username (char(15)), PlayerID (int)

 

LeagueSub - LeagueID (int), Username (nchar(15)), Active (nchar(10))

 

PlayerUpdate - PlayerID (int), Value (int), CurrentScore(int)

[fenway]

Are these many-to-many relationships? You might actually be "joining" in multiple records.