Problem med hockeytabell

Jag har den frågan som räknar ut en hockeytabell

ALTER PROCEDURE cup_getTabell
@groupID int
AS
	Select
		t.Namn,t.ID,
		sum(case when (t.ID = g.homeTeam and g.HomeScore > g.awayScore) 
		or (t.id = g.awayTeam and g.homeScore < g.awayScore) and g.gameEnded = 1 
		and c.playOff = 0 then 1 else 0 end) + 
		sum(case when (t.ID = g.homeTeam and g.HomeScore < g.awayScore) 
		or (t.id = g.awayTeam and g.homeScore > g.awayScore) and g.gameEnded = 1 
		and c.playOff = 0 then 1 else 0 end) + 
		sum(case when ((t.ID = g.homeTeam and g.HomeScore = g.awayScore) 
		and g.gameEnded = 1 and c.playOff = 0 or (t.id = g.awayTeam and g.homeScore = g.awayScore) 
		and g.gameEnded = 1 and c.playOff = 0) then 1 else 0 end) as gamesPlayed,
		sum(case when (t.ID = g.homeTeam and g.HomeScore > g.awayScore) 
		or (t.id = g.awayTeam and g.homeScore < g.awayScore) and g.gameEnded = 1 
		and c.playOff = 0 then 1 else 0 end) as wins,
		sum(case when (t.ID = g.homeTeam and g.HomeScore < g.awayScore) 
		or (t.id = g.awayTeam and g.homeScore > g.awayScore) and g.gameEnded = 1 
		and c.playOff = 0 then 1 else 0 end) as losses,
		sum(case when ((t.ID = g.homeTeam and g.HomeScore = g.awayScore) 
		and g.gameEnded = 1 and c.playOff = 0 
		or (t.id = g.awayTeam and g.homeScore = g.awayScore) 
		and g.gameEnded = 1 and c.playOff = 0) then 1 else 0 end) as ties,
		(sum(case when (t.ID = g.homeTeam and g.HomeScore > g.awayScore) 
		or (t.id = g.awayTeam and g.homeScore < g.awayScore) and g.gameEnded = 1 
		and c.playOff = 0 then 1 else 0 end)*2) + 
		sum(case when (t.ID = g.homeTeam and g.HomeScore = g.awayScore) 
		and g.gameEnded = 1 and c.playOff = 0 or (t.id = g.awayTeam and g.homeScore = g.awayScore) 
		and g.gameEnded = 1 and c.playOff = 0 then 1 else 0 end) as points,
		sum((case when t.ID = g.homeTeam and g.gameEnded = 1 
		and c.playOff = 0 then 1 else 0 end)*homeScore + 
		(case when t.ID = g.awayTeam and g.gameEnded = 1 
		and c.playOff = 0 then 1 else 0 end) * awayScore) as GoalsForward,
		sum((case when t.ID = g.homeTeam and g.gameEnded = 1 
		and c.playOff = 0 then 1 else 0 end)*awayScore + 
		(case when t.ID = g.awayTeam and g.gameEnded = 1 
		and c.playOff = 0 then 1 else 0 end) * homeScore) as GoalsAgainst,
		sum((case when t.ID = g.homeTeam and g.gameEnded = 1 
		and c.playOff = 0 then 1 else 0 end)*homeScore + 
		(case when t.ID = g.awayTeam and g.gameEnded = 1 
		and c.playOff = 0 then 1 else 0 end) * awayScore) - 
		sum((case when t.ID = g.homeTeam and g.gameEnded = 1 
		and c.playOff = 0 then 1 else 0 end)*awayScore + 
		(case when t.ID = g.awayTeam and g.gameEnded = 1 
		and c.playOff = 0 then 1 else 0 end) * homeScore) as goalDifference
	from
	 cup_deltagande_lag as t
		left join cup_games as g
		on (t.ID = g.homeTeam or t.ID = g.awayTeam)
	left join cup_grupper as c
		on c.id = g.groupID
	where
		t.gruppID = @groupID and c.playoff = 0
	group by
		t.ID,t.Namn,c.playoff,t.gruppID
	order by
		points desc, goaldifference Desc, goalsforward desc, t.Namn Asc

Problemet är att den räknar med matcher som inte är slut g.gameended = 0.
lägger jag till where g.gameended = 1 så fungerar det men då visas inte lagen som inte spelat några matcher.

Finns det något sätt att lösa det här på eller måste jag ha med where g.gameended = 1?

Problemet är at tdu tar med c.playoff i where-satsen, vilket gör att LEFT-joinarna tvingas bli INNER-joinar. Använd gärna en derived table som bas, eftersom det blir enklare att få översikt

SELECT t.Namn, 
    t.ID, 
    t.gruppID, 
    s.* 
FROM cup_deltagande_lag AS t, 
    (SELECT t.ID,
        COUNT(CASE WHEN g.gameEnded = 1 AND c.playOff = 0 THEN 1 ELSE 0 END)                                                                          AS gamesPlayed, 
        SUM(CASE WHEN g.gameEnded   = 1 AND c.playOff = 0 AND t.ID = g.homeTeam AND g.HomeScore > g.awayScore THEN 1 ELSE 0 END)                      AS homeWins, 
        SUM(CASE WHEN g.gameEnded   = 1 AND c.playOff = 0 AND t.ID = g.homeTeam AND g.HomeScore < g.awayScore THEN 1 ELSE 0 END)                      AS homeLosses, 
        SUM(CASE WHEN g.gameEnded   = 1 AND c.playOff = 0 AND t.ID = g.awayTeam AND g.HomeScore < g.awayScore THEN 1 ELSE 0 END)                      AS awayWins, 
        SUM(CASE WHEN g.gameEnded   = 1 AND c.playOff = 0 AND t.ID = g.awayTeam AND g.HomeScore > g.awayScore THEN 1 ELSE 0 END)                      AS awayLosses, 
        SUM(CASE WHEN g.gameEnded   = 1 AND c.playOff = 0 AND (t.ID = g.awayTeam OR t.ID=g.homeTeam) AND g.HomeScore = g.awayScore THEN 1 ELSE 0 END) AS [ties], 
        SUM(CASE WHEN g.gameEnded   = 1 AND c.playOff = 0 THEN CASE WHEN t.ID = g.homeTeam THEN g.HomeScore ELSE g.awayScore END ELSE 0 END)          AS goalsForward, 
        SUM(CASE WHEN g.gameEnded   = 1 AND c.playOff = 0 THEN CASE WHEN t.ID = g.awayTeam THEN g.HomeScore ELSE g.awayScore END ELSE 0 END)          AS goalsAgainst 
    FROM cup_deltagande_lag t 
    LEFT JOIN cup_games AS g 
        ON ( (t.ID = g.homeTeam OR t.ID = g.awayTeam) ) 
    LEFT JOIN cup_grupper AS c 
        ON c.id = g.groupID 
    GROUP BY t.ID 
    ) AS s 
WHERE t.gruppID = 1 AND t.ID=s.ID

Löste det. hade missat några parenteser