Batting Game Finder — John Lackey

SELECT /* br:game_finder:9 */     have_pbp,     result_batter,
     name_common as player,
     concat('/players/',left(bio.player_ID,1),'/',bio.player_ID,'.shtml') as player_link,
     A.year_game as year_game,
     A.batter_game as career_game_num,
     batting_team_id,
     pitching_team_id,
     A.date_game,
     A.date_game_number,
     A.game_id as date_game_link,
     A.game_id as game_id,
     pitching_team_id as opp_ID,
     concat('/teams/',pitching_team_id, '/', tgl_stats.year_game, '.shtml') as opp_ID_link,
     batting_team_id as team_id,
     concat('/teams/',batting_team_id,  '/', tgl_stats.year_game, '.shtml') as team_id_link,
     if(homeORvis = 'V','@',' ') as team_homeORaway, tgl_stats.game_length_outs,
     concat( if(tgl_stats.W=1, 'W ', if(tgl_stats.L=1,'L ','T ')), RS,'-',RA) as game_result, tgl_stats.W as teamW, tgl_stats.L as teamL, tgl_stats.RS as teamRS,  tgl_stats.RS, tgl_stats.RA, tgl_stats.RS - tgl_stats.RA as run_diff,
     PA, AB, R, H, 2B, 3B, HR, RBI, BB, IBB, SO, SB, CS, HBP, SH, SF, ROE, GIDP, XI,
     wpa_bat, wpa_li AS wpa_li_bat, re24_bat, leverage_index_avg,
	 IF(AB IS NULL, 'undef', ROUND(IF(AB = 0, NULL, ROUND(H  / AB,10)),3)) AS batting_avg,
	 IF(AB IS NULL, 'undef', ROUND(IF((AB + IFNULL(BB,0) + IFNULL(HBP,0) + IFNULL(SF,0)) = 0, NULL, ROUND((H + IFNULL(BB,0) + IFNULL(HBP,0)) / (AB + IFNULL(BB,0) + IFNULL(HBP,0) + IFNULL(SF,0)),10)),3)) AS onbase_perc,
	 IF(AB IS NULL, 'undef', ROUND(IF(AB = 0, NULL, ROUND((H + 2B + (2 * 3B) + (3 * HR)) / AB, 10)),3)) AS slugging_perc,
	 ROUND(IF((AB + IFNULL(BB,0) + IFNULL(HBP,0) + IFNULL(SF,0)) = 0, NULL, ROUND((H + IFNULL(BB,0) + IFNULL(HBP,0)) / (AB + IFNULL(BB,0) + IFNULL(HBP,0) + IFNULL(SF,0)),10)) + IF(AB = 0, NULL, ROUND((H + 2B + (2 * 3B) + (3 * HR)) / AB, 10)),3) AS onbase_plus_slugging,
     lineup_position as batting_order_position,
     pitcher, catcher, first_base, second_base, third_base, shortstop, left_field, center_field, right_field, designated_hitter, pinch_hitter, pinch_runner,
       birth_year, birth_month, birth_day, death_year,
  if (birth_country = 'Canada' OR birth_country='United States',concat(birth_city,', ',birth_state),if(birth_city is null, birth_country, concat(birth_city,', ',birth_country))) as birth_location,
  if (death_country = 'Canada' OR death_country='United States',concat(death_city,', ',death_state),if(birth_city is null, death_country, concat(death_city,', ',death_country))) as death_location
,
     ROUND(draftkings_points, 2) AS draftkings_points,
     ROUND(fanduel_points, 2) AS fanduel_points
   from bgl as A 
 inner join bio on bio.player_ID=result_batter inner join tgl_stats as tgl_stats on tgl_stats.game_id=A.game_id and tgl_stats.team_id=A.batting_team_id inner join tgl as tgl on tgl_stats.game_id=tgl.game_id inner join players on players.player_ID=bio.player_ID   LEFT JOIN bgl_dfs AS dfs ON A.game_id = dfs.dfs_game_id AND A.result_batter = dfs.dfs_result_batter 

WHERE 
      A.year_game = 2020
  AND A.result_batter = 'lackejo01'
  AND    (1 > 0)   
ORDER BY A.HR DESC, A.date_game DESC, A.date_game_number DESC limit 0,100