SELECT date, person_id, year_id, team_id, player_game_num_season, ts.name_short as team_name_abbr, ts.link as team_name_abbr_link, IFNULL(os.name_short,(SELECT IFNULL(name_short,name) FROM sup_teams WHERE sup_teams.team_id=opp_team_id)) as opp_name_abbr, os.opp_team_link as opp_name_abbr_link, 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, conf_id, NULL AS ranker, player_game_num_career, team_game_num_season, CASE WHEN phase_id = 'nonconference-regular-season' THEN 'REG (Non-Conf)'
			WHEN type = 'reg' AND phase_id LIKE '%-regular-season' THEN 'REG (Conf)'
            WHEN type = 'confpost' THEN 'Conf Championship'
            WHEN tourn_id = 'cfp' THEN sup_phase_seasons.name
            WHEN type = 'bowl' THEN sup_phase_seasons.name
			ELSE ''
       END AS game_type, sacks, tackles_solo+tackles_assists AS tackles_combined, tackles_solo, tackles_assists, tackles_loss, safety_md, def_int, def_int_yds, def_int_td, pass_defended, fumbles_forced, fumbles_rec, fumbles_rec_yds, fumbles_rec_td, CASE WHEN is_bowl_game = 1 THEN bowl_name
			ELSE NULL
       END AS bowl_name
FROM sup_player_games as st
  JOIN sup_games USING (game_id)
  JOIN sup_people USING (person_id)
  JOIN sup_players USING (person_id)
  JOIN (SELECT game_id, year_id, comp_id, phase_id, part_id, team_id, opp_team_id, opp_comp_id, opp_phase_id, team_game_num_season, home_away_neutral, win, loss, tie
FROM sup_team_games
   ) as tg USING (game_id, team_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_short, 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 person_id = ?
   AND year_id = ? 
ORDER BY `date` asc