All right, so for starters, we're going to need to get a lay of the land so to speak with the data that we have. There is a table called players_awards that outputs some good data but, well, some of it is in strange formats... here's our good buddy Henry Riggs:
Code:
SELECT * FROM `modern-ish-baseball`.players_awards
where
league_id = 100
and player_id = 7620;
Code:
7620 100 7 1 0 1956 1956 0 14 5 1
7620 100 7 1 9 1956 1956 0 8 7 1
7620 100 7 1 11 1956 1956 9 5 11 1
7620 100 7 1 0 1957 1957 0 13 5 1
7620 100 7 1 9 1957 1957 0 7 7 1
7620 100 7 1 11 1957 1957 9 4 11 1
7620 100 7 1 9 1958 1958 0 6 7 1
7620 100 7 1 0 1958 1958 0 8 9 1
7620 100 7 1 11 1958 1958 9 4 11 1
7620 100 7 1 5 1958 1958 0 8 11 1
7620 100 7 1 0 1959 1959 0 27 4 1
7620 100 7 1 2 1959 1959 0 1 5 1
7620 100 7 1 9 1959 1959 0 5 7 1
7620 100 7 1 2 1959 1959 0 1 9 1
7620 100 7 1 11 1959 1959 9 5 11 1
7620 100 7 1 5 1959 1959 0 9 11 2
7620 100 7 1 9 1960 1960 0 9 7 1
7620 100 7 1 0 1960 1960 0 22 8 1
7620 100 7 1 2 1960 1960 0 1 9 1
7620 100 7 1 0 1960 1960 0 12 9 1
7620 100 7 1 11 1960 1960 9 2 11 1
7620 100 7 1 5 1960 1960 0 6 11 1
7620 100 7 1 9 1961 1961 0 9 7 1
7620 100 7 1 0 1961 1961 0 21 8 1
7620 100 7 1 2 1961 1961 0 1 9 1
7620 100 7 1 2 1961 1961 0 1 10 1
7620 100 7 1 11 1961 1961 9 30 10 1
7620 100 7 1 9 1962 1962 0 8 7 1
7620 100 7 1 0 1962 1962 0 9 7 1
7620 100 7 1 2 1962 1962 0 1 8 1
7620 100 7 1 11 1962 1962 9 8 11 1
7620 100 7 1 0 1963 1963 0 15 4 1
7620 100 7 1 0 1963 1963 0 10 6 1
7620 100 7 1 9 1963 1963 0 7 7 1
7620 100 7 1 11 1963 1963 9 13 11 1
7620 100 7 1 9 1964 1964 0 5 7 1
7620 100 7 1 0 1965 1965 0 28 6 1
7620 100 7 1 2 1965 1965 0 1 7 1
7620 100 7 1 9 1965 1965 0 11 7 1
7620 100 7 1 0 1965 1965 0 19 7 1
7620 100 7 1 0 1965 1965 0 6 9 1
7620 100 7 1 2 1965 1965 0 1 10 1
7620 100 7 1 11 1965 1965 9 25 10 1
7620 100 7 1 5 1965 1965 0 29 10 3
7620 100 7 1 2 1966 1966 0 1 5 1
7620 100 7 1 0 1966 1966 0 30 5 1
7620 100 7 1 2 1966 1966 0 1 6 1
7620 100 7 1 9 1966 1966 0 10 7 1
7620 100 7 1 2 1966 1966 0 1 10 1
7620 100 7 1 11 1966 1966 9 21 10 1
7620 100 7 1 5 1966 1966 0 25 10 1
7620 100 7 1 9 1967 1967 0 9 7 1
7620 100 7 1 0 1967 1967 0 10 7 1
7620 100 7 1 9 1968 1968 0 7 7 1
7620 100 7 1 0 1968 1968 0 29 7 1
7620 100 7 1 0 1969 1969 0 16 6 1
7620 100 7 1 9 1969 1969 0 21 7 1
7620 100 7 1 0 1969 1969 0 1 9 1
So... ferretting this out from looking at Riggs' "Accomplishments" page on his player card in the game, I can see that the award_id is the important field and it corresponds to the following awards (although I also took a look at some veteran pitchers and super fielders to get the ones that aren't listed for Riggs:
0 - Player of the Week (no thanks)
1 - Pitcher of the Month (nah)
2 - Batter of the Month (also no thanks)
4 - Cy Young ("finish" has to = 1)
5 - MVP (also, "finish" has to = 1)
6 - Rookie of the Year ("finish" = 1)
7 - Gold Glove (position is listed)
9 - All-Star (no position listed)
11- Silver Slugger (position is listed)
13 - Reliever of the Year (also needs finalist = 1)
14 - World Series champion (?)
15 - World Series MVP (? but probably not)
That seems to run the gamut, or at least encapsulates all the awards I want to use.
Okay, so let's start simple. Let's create a data set for Henry Riggs that shows the abbreviation "AS" for every season he went to the ASG...
Code:
SELECT year,
'AS'
FROM `modern-ish-baseball`.players_awards awd
where
league_id = 100
and player_id = 7620
and award_id = 9;
Doing similar things for MVP and Silver Slugger awards:
Code:
SELECT year,
'SS'
FROM `modern-ish-baseball`.players_awards awd
where
league_id = 100
and player_id = 7620
and award_id = 11;
SELECT year,
'MVP'
FROM `modern-ish-baseball`.players_awards awd
where
league_id = 100
and player_id = 7620
and award_id = 5
and finish = 1;
I really wish there was something in the database I could use outside of hardcoding those text values but there does not seem to be. Anyway, we can put these all together into one row per season as well, doing more or less the things we did with the asterisk above... first, though, let's get a general lay of the land in terms of how we want our "intermediate" table to look...
[code]
SELECT year,
'MVP' as mvp,
'AS' as allstar,
'GG' as goldGlove,
'SS' as silverSlugger
FROM `modern-ish-baseball`.players_awards awd
where
league_id = 100
and player_id = 7620
and award_id = 9
and finish = 1;
[code]
That returns a thing that looks like this:
Code:
# year mvp allstar goldGlove silverSlugger
1956 MVP AS GG SS
1957 MVP AS GG SS
1958 MVP AS GG SS
1959 MVP AS GG SS
1960 MVP AS GG SS
1961 MVP AS GG SS
1962 MVP AS GG SS
1963 MVP AS GG SS
1964 MVP AS GG SS
1965 MVP AS GG SS
1966 MVP AS GG SS
1967 MVP AS GG SS
1968 MVP AS GG SS
1969 MVP AS GG SS
I'll add more awards later. ANYWAY, this is obviously not what we want just yet, but we can use IF() statements (also see above) for years that apply...
(also I see that Riggs was awarded with a trip to the game but did not play in it in 1969. BACK TO THE DRAWING BOARD WITH THE PREVIOUS REPORT maybe)
Just to make sure I got the syntax right:
Code:
SELECT year,
if(false, 'MVP', '') as mvp,
'AS' as allstar,
'GG' as goldGlove,
'SS' as silverSlugger
FROM `modern-ish-baseball`.players_awards awd
where
league_id = 100
and player_id = 7620
and award_id = 9
and finish = 1;
Code:
SET @player_id = 7620;
SELECT awd.year,
if(awd.year in (
select year
FROM `modern-ish-baseball`.players_awards awd
where
league_id = 100
and player_id = 7620
and award_id = 5
and finish = 1
)
,
'MVP', '') as mvp,
'AS' as allstar,
'GG' as goldGlove,
'SS' as silverSlugger
FROM `modern-ish-baseball`.players_awards awd
where
awd.league_id = 100
and awd.player_id = player_id;
Oops! Already there's a problem. Removing the "and award_id = 9" clause was necessary because you could have a season where you win the MVP or any of these other awards but don't make it to the All-Star Game. However, if you look up to see how the table is created, it's got one award per line (this is the proper way a table like this should be formed, by the way; players-to-awards is what database nerds call a "many to many" relationship - many players, several different awards - and so you should always use what's called a "mapping table" that maps players - actually players + time periods - to awards). But we don't need one line! We need one line per distinct year. Fortunately, SQL has a way of bringing that data back...
Code:
SET @player_id = 7620;
SELECT awdYears.year as awardYear,
if(awdYears.year in (
select year
FROM `modern-ish-baseball`.players_awards awd
where
league_id = 100
and player_id = 7620
and award_id = 5
and finish = 1
)
,
'MVP', '') as mvp,
'AS' as allstar,
'GG' as goldGlove,
'SS' as silverSlugger
FROM (SELECT DISTINCT year FROM league_history a) as awdYears
order by awardYear
Code:
# awardYear mvp allstar goldGlove silverSlugger
1946 AS GG SS
1947 AS GG SS
1948 AS GG SS
1949 AS GG SS
1950 AS GG SS
1951 AS GG SS
1952 AS GG SS
1953 AS GG SS
1954 AS GG SS
1955 AS GG SS
1956 AS GG SS
1957 AS GG SS
1958 MVP AS GG SS
1959 AS GG SS
1960 MVP AS GG SS
1961 AS GG SS
1962 AS GG SS
1963 AS GG SS
1964 AS GG SS
1965 AS GG SS
1966 MVP AS GG SS
1967 AS GG SS
1968 AS GG SS
1969 AS GG SS
OK, let's talk through that. I created that little subtable called "awdYears" that is nothing but a list of all the different years that are in the players_awards table. Since my league started in 1946, that's basically just 1946 to now. Now that we're asking the players_awards table if the player in question won an award in separate IF statements, we no longer need to join* those tables so we can lop that off as well. As you can see though we now have a list of all years with the word "MVP" in the "MVP" column if the player won that award. Baby steps!
*I should talk about "join" statements... which I'd totally do except that basically everyone just uses LEFT JOIN and if you use anything else and it's not the year 1982 or earlier you are a bad person and should feel bad.
All right, though! So let's add in our code for the other 3 awards (and please note that I went through this one award at a time. I found an issue in there but I think it was related to my cutting the wrong part of code so I won't go through that except to re-re-re-re-reiterate that small chunks = the best. The other nice thing about small chunks is that if you mess something up and can't work your way out of it, you can just undo everything you did up to the point of your last successful run.)
Code:
SET @player_id = 7620;
SELECT awdYears.year as awardYear,
if(awdYears.year in (
select year
FROM `modern-ish-baseball`.players_awards ma
where
ma.league_id = 100
and ma.player_id = @player_id
and ma.award_id = 5
and ma.finish = 1
),
'MVP', '') as mvp,
if(awdYears.year in (
select year
FROM `modern-ish-baseball`.players_awards aa
where
aa.league_id = 100
and aa.player_id = @player_id
and aa.award_id = 9
),
'AS', '') as allstar,
if(awdYears.year in (
select year
FROM `modern-ish-baseball`.players_awards aa
where
aa.league_id = 100
and aa.player_id = @player_id
and aa.award_id = 7
),
'GG', '') as goldglove,
if(awdYears.year in (
select year
FROM `modern-ish-baseball`.players_awards aa
where
aa.league_id = 100
and aa.player_id = @player_id
and aa.award_id = 11
),
'SS', '') as silverslugger
FROM (SELECT DISTINCT a.year FROM league_history a) as awdYears
order by awardYear
Code:
year mvp as gs ss
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956 AS SS
1957 AS SS
1958 MVP AS SS
1959 AS SS
1960 MVP AS SS
1961 AS SS
1962 AS SS
1963 AS SS
1964 AS
1965 AS SS
1966 MVP AS SS
1967 AS
1968 AS
1969 AS
In my league, Hammerin' Hank is not known for his fielding.
Anyway, after doing a bit of further research into what I'm trying to do, I realized that I need to put null values into unused rows rather than empty strings. There's a particular function you can use in MySQL called concat_ws() that will allow us to link up all of these columns but ignore it when there are no values present. The syntax looks like this:
SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!") AS ConcatenatedString;
OK! So... let's quickly create a table that looks like the end result should look, kind of...
Code:
SELECT
awdYears.year,
(select
CONCAT_WS(",", awards.mvp, awards.allstar, awards.gold, awards.silver)
from ( select 'MVP' as mvp, 'AS' as allstar, null as gold, 'SS' as silver) as awards) as awards
FROM (SELECT DISTINCT a.year FROM league_history a) as awdYears
That worked! And now, the brand new MOMENT OF TRUTH:
Code:
SET @player_id = 7620;
SELECT
awdYears.year,
(select
CONCAT_WS(",", awards.mvp, awards.allstar, awards.goldglove, awards.silverslugger)
from
(SELECT awdYears.year as awardYear,
if(awdYears.year in (
select year
FROM `modern-ish-baseball`.players_awards ma
where
ma.league_id = 100
and ma.player_id = @player_id
and ma.award_id = 5
and ma.finish = 1
),
'MVP', null) as mvp,
if(awdYears.year in (
select year
FROM `modern-ish-baseball`.players_awards aa
where
aa.league_id = 100
and aa.player_id = @player_id
and aa.award_id = 9
),
'AS', null) as allstar,
if(awdYears.year in (
select year
FROM `modern-ish-baseball`.players_awards aa
where
aa.league_id = 100
and aa.player_id = @player_id
and aa.award_id = 7
),
'GG', null) as goldglove,
if(awdYears.year in (
select year
FROM `modern-ish-baseball`.players_awards aa
where
aa.league_id = 100
and aa.player_id = @player_id
and aa.award_id = 11
),
'SS', null) as silverslugger
FROM (SELECT DISTINCT a.year FROM league_history a) as awdYears
) as awards where awards.awardyear = awdYears.year
) as awards
FROM (SELECT DISTINCT a.year FROM league_history a) as awdYears;
Code:
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956 AS,SS
1957 AS,SS
1958 MVP,AS,SS
1959 AS,SS
1960 MVP,AS,SS
1961 AS,SS
1962 AS,SS
1963 AS,SS
1964 AS
1965 AS,SS
1966 MVP,AS,SS
1967 AS
1968 AS
1969 AS
Admittedly I made that a lot harder on myself than it needed to be; I copied all of the code into the place where it'd be formatted in the concat_w() function like I was supposed to but I got really badly mixed up by the parentheticals. It happens to everyone! SQL is a massive PITA! Anyway, though, once I get this all set up for the other awards, I'll try and run through how to add it to the batter history screen... which shouldn't be that hard, it's just, a lot more copying and pasting and making the parentheses come out right. Because SQL.