Q:

New to SQL facing a challenge with empty rows

I probably have some design issues as well but if it is possible to do what I am willing to do with the design I have now, I rather do it this way and then keep learning and optimizing. 
I have a gamesresults table that has two rows (one for home and one for away) per game. 
That is,  there's a row gameid 1 with teamid 1 (goals, points, etc) and then another row gameid 1 has an entry for teamid 2 (goals, points etc). 
Gamesresults also has league id (to understand which league the game belongs to) and categoryid (a league can have several categories). 
My PK for gamesresults is gameid + teamid. 
I thought that since I'll be looking at standings more than entering games data, I could duplicate some fields in order to have a simpler query to build the standings. 
I also have a teams table in which I have the teams with Team ID, name, etc and it also includes a categoryid to describe what category the team belongs to. 
My goal is to be able to retrieve aggregate data for goals, points, games played, etc.  for every team and to include the team name. 
Also, if a team from the league has not played any game in the tourney already, I would like to be able to list it with all values of 0. 
I have tried different queries and combinations of left/right joins etc. 
The best I came up with is this:
SELECT 
teams. TeamID,
teams. TeamName,
SUM(PointsAwarded) as PTS,
SUM(TotalGF) as GF,
SUM(TotalGA) as GA,
COUNT(IDGame) as GP,
SUM(CASE WHEN PointsAwarded=2 THEN 1 ELSE 0 END) as W, 
SUM(CASE WHEN PointsAwarded=1 THEN 1 ELSE 0 END) as O, 
SUM(CASE WHEN PointsAwarded=0 THEN 1 ELSE 0 END) as L
from Teams
left join gamesresults on teams. TeamID = gamesresults.idteam
where Teams. TeamcategoryID=1 and gameresults.idleague=1
group by  teams. TeamID, teams. TeamName
order by pts desc

This gives me all teams except those that have not played a game. 
If I do (I remove the leagueid from the where clause):
SELECT 
teams. TeamID,
teams. TeamName,
SUM(PointsAwarded) as PTS,
SUM(TotalGF) as GF,
SUM(TotalGA) as GA,
COUNT(IDGame) as GP,
SUM(CASE WHEN PointsAwarded=2 THEN 1 ELSE 0 END) as W, 
SUM(CASE WHEN PointsAwarded=1 THEN 1 ELSE 0 END) as O, 
SUM(CASE WHEN PointsAwarded=0 THEN 1 ELSE 0 END) as L
from Teams
left join gamesresults on teams. TeamID = gamesresults.idteam
where Teams. TeamcategoryID=1
group by  teams. TeamID, teams. TeamName
order by pts desc

Of course gives me all teams (including those that did not play a game yet), but it counts the games played in every league. 
Any ideas on how I can solve this? 
Thanks! 
Sebastian

A:

If I understand correctly you need to move your condition to the ON clause to preserve the outer join:
left join gamesresults 
on teams. TeamID = gamesresults.idteam 
and gameresults.idleague=1;

