SELECT yearID as year,_uni,IF(replace(_pos,"P","")<>_pos,_playerThrows,_playerBats),playerMaster._nameLast as _nameLast,playerID as playerID,_nameFull,playerSlug,_pos,_playerThrows,_playerBats,_playerHeight as height,_playerHeight as inches,_playerWeight,yearID-_birthYear as age,_yearInCareer,CONCAT(_birthYear,'-',RIGHT(CONCAT('00',_birthMonth),2),'-',RIGHT(CONCAT('00',_birthDay),2)) as birthDate,CONCAT(_birthYear,'-',RIGHT(CONCAT('00',_birthMonth),2),'-',RIGHT(CONCAT('00',_birthDay),2)) as birthSort,REPLACE(CONCAT(
COALESCE(CONCAT(_birthCity,', '),''),
if(_birthState=_birthCity,'',COALESCE(CONCAT(_birthState,', '),'')),
COALESCE(_birthCountry)
),', USA','') as birthPlace,_yearOnTeam,_isFirstSeason,_isFinalSeason,_yearOnTeam=_yearOnTeamMax as isFinalTeamSeason,_isRookieSeason,_yearInCareer>=10 as isVeteran,_isKeyPlayer as isKeyPlayer
FROM playerMaster,
playerStatsB_batAll,
teamMappings
WHERE playerStatsB_batAll.teamID = teamMappings.dbTeamID
AND playerStatsB_batAll.playerID = playerMaster.playerSourceID
AND playerStatsB_batAll.yearID = :yearID
AND teamMappings.teamCode = :teamID
AND playerDataSet = :sportID
AND round = 'REG'
UNION
SELECT yearID as year,_uni,IF(replace(_pos,"P","")<>_pos,_playerThrows,_playerBats),playerMaster._nameLast as _nameLast,playerID as playerID,_nameFull,playerSlug,_pos,_playerThrows,_playerBats,_playerHeight as height,_playerHeight as inches,_playerWeight,yearID-_birthYear as age,_yearInCareer,CONCAT(_birthYear,'-',RIGHT(CONCAT('00',_birthMonth),2),'-',RIGHT(CONCAT('00',_birthDay),2)) as birthDate,CONCAT(_birthYear,'-',RIGHT(CONCAT('00',_birthMonth),2),'-',RIGHT(CONCAT('00',_birthDay),2)) as birthSort,REPLACE(CONCAT(
COALESCE(CONCAT(_birthCity,', '),''),
if(_birthState=_birthCity,'',COALESCE(CONCAT(_birthState,', '),'')),
COALESCE(_birthCountry)
),', USA','') as birthPlace,_yearOnTeam,_isFirstSeason,_isFinalSeason,_yearOnTeam=_yearOnTeamMax as isFinalTeamSeason,_isRookieSeason,_yearInCareer>=10 as isVeteran,_isKeyPlayer as isKeyPlayer
FROM playerMaster,
playerStatsB_pitchAll,
teamMappings
WHERE playerStatsB_pitchAll.teamID = teamMappings.dbTeamID
AND playerStatsB_pitchAll.playerID = playerMaster.playerSourceID
AND playerStatsB_pitchAll.yearID = :yearID
AND teamMappings.teamCode = :teamID
AND playerDataSet = :sportID
AND round = 'REG'
GROUP BY playerSourceID ORDER BY _nameLast,_nameFull;
