[BMW]

This will create a results set with four columns: league_id, SeasonStatus, ExhibitionStatus and AllStarStatus.

Season Status will have either:

• Offseason
• Postseason
• Regular Season
• Preseason
• Spring Training

Exhibition Status will have:

• No Exhibition Games Scheduled
• Exhibition Games Remaining
• All Exhibition Games Finished

All Star Status will have:

• No All Star Game Scheduled
• Before All Star Break
• After All Star Break

SELECT L.league_id, 

CASE 
WHEN post.Ct IS NOT NULL AND postexists.Ct IS NULL THEN 'Offseason' 
WHEN post.Ct IS NULL AND postexists.Ct IS NULL AND reg.Ct IS NOT NULL AND regexists.Ct IS NULL THEN 'Offseason' 
WHEN post.Ct IS NOT NULL AND postexists.Ct IS NOT NULL THEN 'Postseason' 
WHEN reg.Ct IS NOT NULL AND regexists.Ct IS NOT NULL THEN 'Regular Season' 
WHEN spr.Ct IS NULL AND springexists.Ct IS NULL THEN 'Preseason' 
WHEN spr.Ct IS NOT NULL THEN 'Spring Training' 
ELSE 'Preseason' END AS SeasonStatus,

CASE 
WHEN exibitionexists.Ct IS NULL AND exibition.Ct IS NULL THEN 'No Exhibition Games Scheduled'
WHEN exibitionexists.Ct IS NOT NULL AND exibition.Ct IS NOT NULL THEN 'Exhibition Games Remaining'
ELSE 'All Exhibition Games Finished' END AS ExhibitionStatus,

CASE 
WHEN allstarexists.Ct IS NULL AND allstar.Ct IS NULL THEN 'No All Star Game Scheduled'
WHEN allstarexists.Ct IS NOT NULL AND allstar.Ct IS NOT NULL THEN 'Before All Star Break'
ELSE 'After All Star Break' END AS AllStar_Status

FROM leagues L 

LEFT JOIN (SELECT g.league_id, COUNT(g.game_id) as Ct FROM games g WHERE g.played = 1 AND g.game_type = 0 GROUP BY g.league_id) AS reg ON L.league_id = reg.league_id 

LEFT JOIN (SELECT g.league_id, COUNT(g.game_id) as Ct FROM games g WHERE g.played = 1 AND g.game_type = 1 GROUP BY g.league_id) AS exibition ON L.league_id = exibition.league_id 

LEFT JOIN (SELECT g.league_id, COUNT(g.game_id) as Ct FROM games g WHERE g.played = 1 AND g.game_type = 2 GROUP BY g.league_id) AS spr ON L.league_id = spr.league_id 

LEFT JOIN (SELECT g.league_id, COUNT(g.game_id) as Ct FROM games g WHERE g.played = 1 AND g.game_type = 3 GROUP BY g.league_id) AS post ON L.league_id = post.league_id 

LEFT JOIN (SELECT g.league_id, COUNT(g.game_id) as Ct FROM games g WHERE g.played = 1 AND g.game_type = 4 GROUP BY g.league_id) AS allstar ON L.league_id = allstar.league_id 

LEFT JOIN (SELECT g.league_id, COUNT(g.game_id) as Ct FROM games g WHERE g.played = 0 AND g.game_type = 4 GROUP BY g.league_id) AS allstarexists ON L.league_id = allstarexists.league_id 

LEFT JOIN (SELECT g.league_id, COUNT(g.game_id) as Ct FROM games g WHERE g.played = 0 AND g.game_type = 3 GROUP BY g.league_id) AS postexists ON L.league_id = postexists.league_id 

LEFT JOIN (SELECT g.league_id, COUNT(g.game_id) as Ct FROM games g WHERE g.played = 0 AND g.game_type = 2 GROUP BY g.league_id) AS springexists ON L.league_id = springexists.league_id 

LEFT JOIN (SELECT g.league_id, COUNT(g.game_id) as Ct FROM games g WHERE g.played = 0 AND g.game_type = 1 GROUP BY g.league_id) AS exibitionexists ON L.league_id = exibitionexists.league_id 

LEFT JOIN (SELECT g.league_id, COUNT(g.game_id) as Ct FROM games g WHERE g.played = 0 AND g.game_type = 0 GROUP BY g.league_id) AS regexists ON L.league_id = regexists.league_id