Query Library

Casey Trauer submitted this query on: March 04, 2010 1:59pm EST
TITLE Divisional standings for MLB
SPORTS Baseball
QUERY SELECT 
     standing_affiliations.affiliation_key as league_key,
dn_division.full_name,
     standing_subgroup_affiliations.affiliation_key as division_key,
dn_team.full_name,
     teams.team_key,
     standing_subgroups.duration_scope,
     standing_subgroups.alignment_scope,
     standing_subgroups.competition_scope,
     outcome_totals.rank,
     outcome_totals.wins,
     outcome_totals.losses,
     outcome_totals.winning_percentage,
     outcome_totals.events_played,
     outcome_totals.games_back,
     outcome_totals.streak_type,
     outcome_totals.streak_total
FROM standings
INNER JOIN standing_subgroups ON standing_subgroups.standing_id = standings.id
INNER JOIN outcome_totals ON outcome_totals.standing_subgroup_id = standing_subgroups.id
INNER JOIN sub_seasons ON standings.sub_season_id = sub_seasons.id
INNER JOIN seasons ON sub_seasons.season_id = seasons.id
INNER JOIN affiliations as standing_affiliations ON standings.affiliation_id = standing_affiliations.id
INNER JOIN affiliations as standing_subgroup_affiliations ON standing_subgroups.affiliation_id = standing_subgroup_affiliations.id 
INNER JOIN affiliations as season_affiliations ON seasons.league_id = season_affiliations.id
INNER JOIN teams ON outcome_totals.outcome_holder_id = teams.id
INNER JOIN display_names dn_team ON (dn_team.entity_type = 'teams' AND dn_team.entity_id = teams.id)
INNER JOIN display_names dn_division ON (dn_division.entity_type = 'affiliations' AND dn_division.entity_id = standing_subgroup_affiliations.id)
INNER JOIN publishers ON seasons.publisher_id = publishers.id 
WHERE standing_affiliations.affiliation_key = 'l.mlb.com'
     AND standing_affiliations.affiliation_type = 'league'
     AND seasons.season_key = '2009'
     AND sub_seasons.sub_season_type = 'season-regular'
     AND outcome_totals.outcome_holder_type = 'teams'
     AND publishers.publisher_key = 'sportsnetwork.com' 
ORDER BY
     league_key,
     division_key,
     outcome_totals.rank,
     standing_subgroups.competition_scope,
     standing_subgroups.alignment_scope
DESCRIPTION This query will return several rows per team to describe all sorts of standings splits: all, events-home, events-away, vs. conference. You can narrow it down to just, say, overall standings by specifying:

WHERE standing_subgroups.alignment_scope IS NULL
AND standing_subgroups.competition_scope = 'all'
DBs TESTED MySQL
ADDITIONAL NOTES
Comments
on September 20, 2010 11:23am EDT Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT 
     standing_affiliations.affiliation_key as league_key,
dn_division.full_name,
     standing_subgroup_affiliations.affiliation_key as division_key,
dn_team.full_name,
     teams.team_key,
     standing_subgroups.duration_scope,
     standing_subgroups.alignment_scope,
     standing_subgroups.competition_scope,
     outcome_totals.rank,
     outcome_totals.wins,
     outcome_totals.losses,
     outcome_totals.winning_percentage,
     outcome_totals.events_played,
     outcome_totals.games_back,
     outcome_totals.streak_type,
     outcome_totals.streak_total
FROM standings
INNER JOIN standing_subgroups ON standing_subgroups.standing_id = standings.id
INNER JOIN outcome_totals ON outcome_totals.standing_subgroup_id = standing_subgroups.id
INNER JOIN sub_seasons ON standings.sub_season_id = sub_seasons.id
INNER JOIN seasons ON sub_seasons.season_id = seasons.id
INNER JOIN affiliations as standing_affiliations ON standings.affiliation_id = standing_affiliations.id
INNER JOIN affiliations as standing_subgroup_affiliations ON standing_subgroups.affiliation_id = standing_subgroup_affiliations.id 
INNER JOIN affiliations as season_affiliations ON seasons.league_id = season_affiliations.id
INNER JOIN teams ON outcome_totals.outcome_holder_id = teams.id
INNER JOIN display_names dn_team ON (dn_team.entity_type = 'teams' AND dn_team.entity_id = teams.id)
INNER JOIN display_names dn_division ON (dn_division.entity_type = 'affiliations' AND dn_division.entity_id = standing_subgroup_affiliations.id)
INNER JOIN publishers ON seasons.publisher_id = publishers.id 
WHERE standing_affiliations.affiliation_key = 'l.mlb.com'
     AND standing_affiliations.affiliation_type = 'league'
     AND seasons.season_key = '2010'
     AND sub_seasons.sub_season_type = 'season-regular'
     AND outcome_totals.outcome_holder_type = 'teams'
     AND publishers.publisher_key = 'sportsnetwork.com' 
AND standing_subgroups.alignment_scope IS NULL
AND standing_subgroups.competition_scope = 'all'
AND standings.standing_type = 'division'
ORDER BY
     league_key,
     division_key,
     outcome_totals.rank,
     standing_subgroups.competition_scope,
     standing_subgroups.alignment_scope

"Adds a WHERE clause: AND standings.standing_type = 'division'.

This may be necessary to distinguish between division and conference standings, such as wildcard standings."