Hi all. Thread #21998 by a newbie with a query problem.
Ok, here's my situation. I'm boiling this down to the most simple case for the example.
Table Players1, a list of sports players:
PlyrID, TeamID, Fname, Lname
1, 1, Joe, Blow
2, 1, Frank, Smith
3, 1, Harold, Hanks
As these players play their sport, they accumulate stats. Thus table Scores1:
ScoreID, TeamID, PlyrID, sGame, pts, assts
1, 1, 1, 1, 12, 4 (Joe Blow scores 12 pts, 4 assts, in game 1 for team 1)
2, 1, 1, 2, 10, 3 (Joe Blow scores 10 pts, 3 assts, in game 2 for team 1)
3, 1, 3, 15, 5, 2 (Hanks scores 5 pts, 2 assts, in game 15 for team 1)
Now, what I want is to generate a summary for a given team in a certain time period. The tricky part is I want it include all players for a team, not just those who played. For example, let's say the season is 20 games long, and I'm wanting a list of first half stats. So, Hanks score above is in game 15, so in the list he should be zeros. Thus, result should end up looking like:
[pre]
Name Games played Pts Assts
Joe Blow 2 22 7
Harold Hanks 0 0 0
Frank Smith 0 0 0
[/pre]
I can get simple ones to work. For example, this:
SELECT plyrID, FName, LName, COUNT(Scores1.sGame) AS GP, SUM(Scores1.Assts), SUM(Scores1.Pts)
FROM Players1
LEFT JOIN Scores1 ON (Players1.PlyrID = Scores1.PlyrID) AND (Players1.TeamID = Scores1.TeamID)
WHERE (Players1.TeamID = 1 )
works fine, as I can use the Nulls to generate 0's.
[pre]
Name Games played Pts Assts
Joe Blow 2 22 7
Harold Hanks 1 5 2
Frank Smith NULL NULL NULL
[/pre]
But as soon as I try and include conditions like game <= 10...
SELECT plyrID, FName, LName, COUNT(Scores1.sGame) AS GP, SUM(Scores1.Assts), SUM(Scores1.Pts)
FROM Players1
LEFT JOIN Scores1 ON (Players1.PlyrID = Scores1.PlyrID) AND (Players1.TeamID = Scores1.TeamID)
WHERE (Players1.TeamID = 1 ) and (Scores1.sGame <= 10)
[pre]
Name Games played Pts Assts
Joe Blow 2 22 7
[/pre]
Smith and Hanks are gone, rather than NULL.
I've tried a million things, none of them work. I know I can work around this, query the database for just a list of players, another one for scores, scan them separately and build up the info from there in php, but that's against my nature. Working around a problem and just avoiding it aren't me.
