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(team_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 pts-opp_pts+IFNULL(pts,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, (SELECT CONCAT(IF(win=1,'W',IF(loss=1,'L','T')),' ',pts,'-',opp_pts,IF(overtimes>0, ' (', ''),IF(overtimes>1, overtimes, ''),IF(overtimes>0, 'OT)', '')) FROM sup_team_games tg2 WHERE tg2.game_id=st.game_id AND tg2.team_id=st.team_id) as game_result, conf_id, NULL AS ranker, player_game_num_career, team_game_num_season, CASE WHEN type = 'post' AND phase_id LIKE 'NCAA%' THEN 'NCAA'
			WHEN type = 'post' AND phase_id LIKE 'NIT%' THEN 'NIT'
            WHEN type = 'post' THEN UPPER(phase_id)
            WHEN type = 'confpost' THEN 'CTOURN'
			WHEN phase_id = 'nonconference-regular-season' THEN 'REG (Non-Conf)'
			ELSE 'REG (Conf)'
       END AS game_type, IF(is_starter,'*','') AS is_starter, mp, fg, fga, IF(fga, fg / fga, NULL) as fg_pct_csk, TRIM(LEADING '0' FROM ROUND(IF(fga, fg / fga, NULL),3)) AS fg_pct, fg3, fg3a, IF(fg3a, fg3 / fg3a, NULL) as fg3_pct_csk, TRIM(LEADING '0' FROM ROUND(IF(fg3a, fg3 / fg3a, NULL),3)) AS fg3_pct, (fg - IFNULL(fg3, 0)) AS fg2, (fga - IFNULL(fg3a, 0)) AS fg2a, (fg - IFNULL(fg3, 0)) / (fga - IFNULL(fg3a, 0)) as fg2_pct_csk, TRIM(LEADING '0' FROM ROUND((fg - IFNULL(fg3, 0)) / (fga - IFNULL(fg3a, 0)),3)) AS fg2_pct, IF(fga, (fg + 0.5 * IFNULL(fg3, 0)) / fga, NULL) as efg_pct_csk, TRIM(LEADING '0' FROM ROUND(IF(fga, (fg + 0.5 * IFNULL(fg3, 0)) / fga, NULL),3)) AS efg_pct, ft, fta, IF(fta, ft / fta, NULL) as ft_pct_csk, TRIM(LEADING '0' FROM ROUND(IF(fta, ft / fta, NULL),3)) AS ft_pct, orb, trb - orb AS drb, trb, ast, stl, blk, tov, pf, pts, pts + 0.4 * fg - 0.7 * fga - 0.4 * (fta - ft) + 0.7 * orb + 0.3 * (trb - orb) + stl + 0.7 * ast + 0.7 * blk - 0.4 * pf - tov as game_score_csk, ROUND(pts + 0.4 * fg - 0.7 * fga - 0.4 * (fta - ft) + 0.7 * orb + 0.3 * (trb - orb) + stl + 0.7 * ast + 0.7 * blk - 0.4 * pf - tov,1) AS game_score
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