SQL Case statement with multi left outer joins

Question

Is it possible to do case statements and to left outer joins like I am doing below? I put comments on what I am trying to achieve.

SELECT
    eventGame.Id,
    eventGame.[Type] AS GameType
FROM GrassrootsHoops.EventGame eventGame
LEFT OUTER JOIN (SELECT
    divisionGameTeamResult.Id,
    divisionGameTeamResult.GameId,
    COALESCE(divisionPool.Name, divisionTeamPoolPoolBracket.Name) AS DivisionPoolName
FROM GrassrootsHoops.DivisionGameTeamResult divisionGameTeamResult
LEFT OUTER JOIN

-- CASE eventGame.[Type] = 1 THEN

GrassrootsHoops.DivisionTeamPoolGame divisionTeamPoolGame
    ON divisionTeamPoolGame.Id = divisionGameTeamResult.Id
LEFT OUTER JOIN GrassrootsHoopS.DivisionTeamPool divisionTeamPool
    ON divisionTeamPool.Id = divisionTeamPoolGame.DivisionTeamPoolId
LEFT OUTER JOIN GrassrootsHoops.DivisionPool divisionPool
    ON divisionPool.Id = divisionTeamPool.DivisionPoolId
LEFT OUTER JOIN GrassrootsHoops.DivisionPoolSettings divisionPoolSettings
    ON divisionPool.Id = divisionPoolSettings.Id
LEFT OUTER JOIN GrassrootsHoops.DivisionTeam divisionTeam
    ON divisionTeam.Id = divisionTeamPool.DivisionTeamId
LEFT OUTER JOIN GrassrootsHoops.Team team
    ON team.Id = divisionTeam.Id
LEFT OUTER JOIN

-- CASE eventGame.[Type] = 2 THEN

GrassrootsHoops.DivisionBracketParticipant divisionBracketGameParticipant
    ON divisionBracketGameParticipant.DivisionGameTeamResultId = divisionGameTeamResult.Id
LEFT OUTER JOIN GrassrootsHoops.DivisionBracketParticipantPart divisionBracketParticipantPart
    ON divisionBracketParticipantPart.Id = divisionBracketGameParticipant.DivisionBracketParticipantPartId
LEFT OUTER JOIN GrassrootsHoops.DivisionBracketParticipantTeam divisionBracketGameParticipantTeam
    ON divisionBracketGameParticipantTeam.Id = divisionBracketGameParticipant.Id
LEFT OUTER JOIN GrassrootsHoops.DivisionTeam divisionTeamBracket
    ON divisionTeamBracket.Id = divisionBracketGameParticipantTeam.DivisionTeamId
LEFT OUTER JOIN GrassrootsHoops.Team teamBracket
    ON teamBracket.Id = divisionTeamBracket.Id
LEFT OUTER JOIN GrassrootsHoops.DivisionBracketParticipantBracket divisionBracketParticipantBracket
    ON divisionBracketParticipantBracket.Id = divisionBracketGameParticipant.Id
LEFT OUTER JOIN GrassrootsHoops.DivisionBracket divisionBracketParticipantBracketBracket
    ON divisionBracketParticipantBracketBracket.Id = divisionBracketParticipantBracket.DivisionBracketId
LEFT OUTER JOIN GrassrootsHoops.DivisionBracketParticipantPool divisionBracketGameParticipantPool
    ON divisionBracketGameParticipantPool.Id = divisionBracketGameParticipant.Id
LEFT OUTER JOIN GrassrootsHoops.DivisionPool divisionPoolBracket
    ON divisionPoolBracket.Id = divisionBracketGameParticipantPool.DivisionPoolId
LEFT OUTER JOIN GrassrootsHoops.DivisionBracketParticipantTeamPool divisionBracketGameParticipantTeamPool
    ON divisionBracketGameParticipantTeamPool.Id = divisionBracketGameParticipant.Id
LEFT OUTER JOIN GrassrootsHoops.DivisionTeamPool divisionTeamPoolBracket
    ON divisionTeamPoolBracket.Id = divisionBracketGameParticipantTeamPool.DivisionTeamPoolId
LEFT OUTER JOIN GrassrootsHoops.DivisionPool divisionTeamPoolPoolBracket
    ON divisionTeamPoolPoolBracket.Id = divisionTeamPoolBracket.DivisionPoolId
LEFT OUTER JOIN GrassrootsHoops.DivisionTeam divisionTeamPoolTeamBracket
    ON divisionTeamPoolTeamBracket.Id = divisionTeamPoolBracket.DivisionTeamId
LEFT OUTER JOIN GrassrootsHoops.Team teamPoolTeamBracket
    ON teamPoolTeamBracket.Id = divisionTeamPoolTeamBracket.Id
WHERE divisionGameTeamResult.HomeTeam = 1) homeTeam
    ON homeTeam.GameId = eventGame.GameId

Answer 1

You can't do case statements with outer joins. I think you can do what you want, though by using a UNION. Here's an example using a simpler schema where we are doing two separate joins based on the type of game.

  SELECT
    Game.Id,
    PoolResult.Score
  FROM
    Game
  JOIN PoolResult
    ON PoolResult.GameId = Game.Id
  WHERE
    Game.GameType = 1
UNION
  SELECT
    Game.Id,
    BracketResult.Score
  FROM
    Game
  JOIN BracketResult
    ON BracketResult.GameId = Game.Id
  WHERE
    Game.GameType = 2

(Here's a working version at SQL Fiddle)

For your query, you just want to write two completely separate queries one for each type of game. Once you have that working for each separate query, just stick a UNION in between them and the results will be concatenated together. Here's your query modified to use a UNION. I don't have an easy way to check it for errors, so there's probably some minor errors

  SELECT
    divisionGameTeamResult.Id,
    eventGame.[Type] AS GameType
  FROM
    GrassrootsHoops.EventGame eventGame
  JOIN GrassrootsHoops.DivisionGameTeamResult divisionGameTeamResult
    ON (divisionGameTeamResult.GameId = eventGame.GameId
  JOIN GrassrootsHoops.DivisionTeamPoolGame divisionTeamPoolGame
    ON divisionTeamPoolGame.Id = divisionGameTeamResult.Id
  JOIN GrassrootsHoopS.DivisionTeamPool divisionTeamPool
    ON divisionTeamPool.Id = divisionTeamPoolGame.DivisionTeamPoolId
  JOIN GrassrootsHoops.DivisionPool divisionPool
    ON divisionPool.Id = divisionTeamPool.DivisionPoolId
  JOIN GrassrootsHoops.DivisionPoolSettings divisionPoolSettings
    ON divisionPool.Id = divisionPoolSettings.Id
  JOIN GrassrootsHoops.DivisionTeam divisionTeam
    ON divisionTeam.Id = divisionTeamPool.DivisionTeamId
  JOIN GrassrootsHoops.Team team
    ON team.Id = divisionTeam.Id
  WHERE
    divisionGameTeamResult.HomeTeam = 1
  AND
    eventGame.[Type] = 1
UNION
  SELECT
    divisionGameTeamResult.Id,
    eventGame.[Type] AS GameType
  FROM
    GrassrootsHoops.EventGame eventGame
  JOIN GrassrootsHoops.DivisionGameTeamResult divisionGameTeamResult
    ON (divisionGameTeamResult.GameId = eventGame.GameId)
  JOIN GrassrootsHoops.DivisionBracketParticipant divisionBracketGameParticipant
    ON divisionBracketGameParticipant.DivisionGameTeamResultId = divisionGameTeamResult.Id
  JOIN GrassrootsHoops.DivisionBracketParticipantPart divisionBracketParticipantPart
    ON divisionBracketParticipantPart.Id = divisionBracketGameParticipant.DivisionBracketParticipantPartId
  JOIN GrassrootsHoops.DivisionBracketParticipantTeam divisionBracketGameParticipantTeam
    ON divisionBracketGameParticipantTeam.Id = divisionBracketGameParticipant.Id
  JOIN GrassrootsHoops.DivisionTeam divisionTeamBracket
    ON divisionTeamBracket.Id = divisionBracketGameParticipantTeam.DivisionTeamId
  JOIN GrassrootsHoops.Team teamBracket
    ON teamBracket.Id = divisionTeamBracket.Id
  JOIN GrassrootsHoops.DivisionBracketParticipantBracket divisionBracketParticipantBracket
    ON divisionBracketParticipantBracket.Id = divisionBracketGameParticipant.Id
  JOIN GrassrootsHoops.DivisionBracket divisionBracketParticipantBracketBracket
    ON divisionBracketParticipantBracketBracket.Id = divisionBracketParticipantBracket.DivisionBracketId
  JOIN GrassrootsHoops.DivisionBracketParticipantPool divisionBracketGameParticipantPool
    ON divisionBracketGameParticipantPool.Id = divisionBracketGameParticipant.Id
  JOIN GrassrootsHoops.DivisionPool divisionPoolBracket
    ON divisionPoolBracket.Id = divisionBracketGameParticipantPool.DivisionPoolId
  JOIN GrassrootsHoops.DivisionBracketParticipantTeamPool divisionBracketGameParticipantTeamPool
    ON divisionBracketGameParticipantTeamPool.Id = divisionBracketGameParticipant.Id
  JOIN GrassrootsHoops.DivisionTeamPool divisionTeamPoolBracket
    ON divisionTeamPoolBracket.Id = divisionBracketGameParticipantTeamPool.DivisionTeamPoolId
  JOIN GrassrootsHoops.DivisionPool divisionTeamPoolPoolBracket
    ON divisionTeamPoolPoolBracket.Id = divisionTeamPoolBracket.DivisionPoolId
  JOIN GrassrootsHoops.DivisionTeam divisionTeamPoolTeamBracket
    ON divisionTeamPoolTeamBracket.Id = divisionTeamPoolBracket.DivisionTeamId
  JOIN GrassrootsHoops.Team teamPoolTeamBracket
    ON teamPoolTeamBracket.Id = divisionTeamPoolTeamBracket.Id
  WHERE
    divisionGameTeamResult.HomeTeam = 1
  AND
    eventGame.[Type] = 1