Touchdown Finder

/* mysql8 */
SELECT /* player_td_finder */ year_id,
  game_num,
  game_date,
  CONCAT('/boxscores/' , ifnull(game_id, concat(DATE_FORMAT(game_date,'%Y%m%d'),'0',IF(is_home_game=1 OR (is_home_game IS NULL AND team_id<opp_id),team_id,opp_id))),'.htm') AS game_date_link,
  DAYNAME(game_date) AS day_name,
  team_id,
  IF(is_home_game IS NULL,'N',IF(is_home_game=1,'','@')) AS game_location,
  opp_id,
  CONCAT(IF(points > points_opp, 'W', IF(points < points_opp, 'L', 'T')), ' ', points, '-', points_opp) AS game_result, 
  points - points_opp AS game_result_csk,
  quarter,
  score_type,
  distance,
  td_scorer,
  td_passer,
  CONCAT_WS(" ",master_scorer.name_first, master_scorer.name_last) AS scorer,
  CONCAT('/players/',UPPER(LEFT(master_scorer.player_id,1)),'/',master_scorer.player_id,'.htm') AS scorer_link,
  IF(master_scorer.is_hall_of_famer = 1, '*', '') AS scorer_link_post,
  CONCAT_WS(",",master_scorer.name_last, master_scorer.name_first) AS scorer_csk,
  replace(scorer_games_ages.year_days, '.', '-') as scorer_age,
  CONCAT_WS(" ",master_qb.name_first, master_qb.name_last) AS qb,
  CONCAT('/players/',UPPER(LEFT(master_qb.player_id,1)),'/',master_qb.player_id,'.htm') AS qb_link,
  IF(master_qb.is_hall_of_famer = 1, '*', '') AS qb_link_post,
  CONCAT_WS(",",master_qb.name_last, master_qb.name_first) AS qb_csk,
  replace(passer_games_ages.year_days, '.', '-') as qb_age,
  CONCAT_WS(" ",master_kick.name_first, master_kick.name_last) AS kicker,
  CONCAT('/players/',UPPER(LEFT(master_kick.player_id,1)),'/',master_kick.player_id,'.htm') AS kicker_link,
  CONCAT_WS(",",master_kick.name_last, master_kick.name_first) AS kicker_csk,
  case xpa_type   
  when 'p' then concat(if(xp_passer != '',(select concat(name_first, ' ', name_last) from master where master.player_id = xp_passer),''), ' pass ',
  if(is_success_xpa,concat('complete to ', (select concat(name_first, ' ', name_last) from master where master.player_id = xp_scorer)), 'failed'))
  when 'r' then concat(if(xp_scorer != '',(select concat(name_first, ' ', name_last) from master where master.player_id = xp_scorer),''), ' rush ', if(is_success_xpa,'succeeded','failed'))   
  when 'k' then concat(if(xp_kicker != '',(select concat(name_first, ' ', name_last) from master where master.player_id = xp_kicker),''), ' kick ', if(is_success_xpa,'succeeded','failed'))   else '' end as conv_string,
  CONCAT_WS(' ', CASE WHEN score_before > score_before_opp THEN 'Lead'
  WHEN score_before < score_before_opp THEN 'Trail'
  ELSE 'Tied'
  END,
  CONCAT_WS('-', score_before, score_before_opp)) AS score_before,
  score_before - score_before_opp AS score_before_csk,
  CONCAT(CASE WHEN score_after > score_after_opp THEN 'Lead'
  WHEN score_after < score_after_opp THEN 'Trail'
  ELSE 'Tied'
  END,
  ' ',
  CONCAT_WS('-', score_after, score_after_opp),
  IF(is_goahead = 1, '*', '')) AS score_after,
  score_after - score_after_opp AS score_after_csk,
  score_before - score_before_opp AS diff,
  is_tying,
  is_goahead,
   week_num
FROM scoring
  LEFT JOIN master AS master_kick
  ON scoring.kicker = master_kick.player_id
  LEFT JOIN master AS master_scorer
  ON scoring.td_scorer = master_scorer.player_id
  LEFT JOIN master AS master_qb
  ON ifnull(scoring.td_passer,scoring.int_passer) = master_qb.player_id
  left join (select years, year_days, game_id, player_id as td_scorer from games_ages) as scorer_games_ages using (td_scorer, game_id)
  left join (select years, year_days, game_id, player_id as td_passer from games_ages) as passer_games_ages using (td_passer, game_id)
WHERE td_scorer != '' AND year_id >= 1920 AND year_id <= 2025 AND is_playoff_game = 0 AND (score_type in ('pass','rush')) AND (ifnull(distance,0) BETWEEN 0 AND 110)
ORDER BY distance DESC
LIMIT 0, 20


$VAR1 = [];