SELECT date, team_game_num_season, team_id, year_id, game_id, sup_games.link, IF(home_away_neutral="H","",IF(home_away_neutral="A","@","N")) as game_location, (SELECT points-points_opp+ifnull(points,0)/100 FROM sup_team_games tg2 WHERE tg2.game_id=st.game_id AND tg2.team_id=st.team_id) as game_result_csk, CONCAT((SELECT CONCAT(IF(win=1,'W',IF(loss=1,'L','T')),' ',points,'-',points_opp) FROM sup_team_games tg2 WHERE tg2.game_id=st.game_id AND tg2.team_id=st.team_id),IF(overtimes>0,IF(overtimes>1,CONCAT(' (',overtimes,'OT)'),' (OT)'),'')) as game_result, os.opp_team_link as opp_name_abbr_link, conf_id, NULL AS ranker, IFNULL(os.name_short,(SELECT IFNULL(name_short,name) FROM sup_teams WHERE sup_teams.team_id=opp_team_id)) AS opp_name_abbr, IF(win > 0, "W", IF(loss > 0, "L", IF(tie > 0, "T", ""))) AS team_game_result, points, points_opp, IF(overtimes, IF(overtimes>1, CONCAT(overtimes,"OT"), "OT"), "") AS overtimes, pass_cmp, pass_att, IF(pass_att, pass_cmp/pass_att, NULL) as pass_cmp_pct_csk, ROUND(100 * (IF(pass_att, pass_cmp/pass_att, NULL)),1) AS pass_cmp_pct, pass_yds, pass_td, IF(pass_att, ((pass_yds)/(pass_att)), NULL) as pass_yds_per_att_csk, ROUND(IF(pass_att, ((pass_yds)/(pass_att)), NULL),1) AS pass_yds_per_att, IF(pass_att,((pass_yds + (20.0 * pass_td) - (45.0 * pass_int))/(pass_att)),NULL) as pass_adj_yds_per_att_csk, ROUND(IF(pass_att,((pass_yds + (20.0 * pass_td) - (45.0 * pass_int))/(pass_att)),NULL),2) AS pass_adj_yds_per_att, IF(pass_att > 0, (8.4 * pass_yds + 330 * pass_td - 200 * pass_int + 100 * pass_cmp) / pass_att, NULL) as pass_rating_csk, ROUND(IF(pass_att > 0, (8.4 * pass_yds + 330 * pass_td - 200 * pass_int + 100 * pass_cmp) / pass_att, NULL),1) AS pass_rating, rush_att, rush_yds, IF(rush_att, rush_yds/rush_att, NULL) as rush_yds_per_att_csk, ROUND(IF(rush_att, rush_yds/rush_att, NULL),1) AS rush_yds_per_att, rush_td, pass_att + rush_att AS tot_plays, rush_yds + pass_yds AS tot_yds, (rush_yds + pass_yds)/(pass_att + rush_att) as tot_yds_per_play_csk, ROUND((rush_yds + pass_yds)/(pass_att + rush_att),1) AS tot_yds_per_play, fga, fgm, xpa, xpm, punt, punt_yds, first_down_pass, first_down_rush, first_down_penalty, first_down, penalties, penalties_yds, fumbles_lost, pass_int, pass_int+fumbles_lost AS turnovers, CASE WHEN is_bowl_game = 1 THEN bowl_name
			ELSE NULL
       END AS bowl_name
FROM sup_team_games as st
  JOIN sup_games USING (game_id)
  JOIN sup_phase_seasons USING (comp_id, phase_id, year_id)
  JOIN sup_comp_seasons USING (comp_id, year_id)
  JOIN (SELECT team_id, year_id, name_abbr, name, link
FROM sup_team_seasons
  
GROUP BY team_id, year_id ) as ts USING (team_id, year_id)
  LEFT JOIN (SELECT team_id as opp_team_id, link as opp_team_link, year_id, name_abbr, name_short
FROM sup_team_seasons
  
GROUP BY team_id, year_id ) as os USING (opp_team_id, year_id)
  JOIN (SELECT team_id, comp_id, year_id, phase_id, name_short, conf_id, link
FROM sup_team_seasons
   ) as sts USING (team_id, comp_id, year_id, phase_id)
  LEFT JOIN (SELECT parent_conf_id, conf_id, conf_name, conf_abbr, link as conf_abbr_link, comp_id, year_id
FROM sup_conference_seasons
   ) as team_conf USING (conf_id, comp_id, year_id)
 WHERE team_id = ?
   AND year_id = ? 
ORDER BY `date` asc