Query Library

Casey Trauer submitted this query on: July 23, 2008 4:38pm EDT
TITLE Get all standings for all MLB teams
SPORTS Baseball
QUERY SELECT 
     standing_affiliations.affiliation_key as league_key,
     standing_subgroup_affiliations.affiliation_key as division_key,
     teams.team_key,
     standing_subgroups.alignment_scope,
     standing_subgroups.competition_scope,
     outcome_totals.rank,
     outcome_totals.wins,
     outcome_totals.losses,
     outcome_totals.ties,
     outcome_totals.winning_percentage,
     outcome_totals.events_played,
     outcome_totals.games_back
FROM
     standings, 
     standing_subgroups, 
     outcome_totals,
      affiliations as standing_affiliations, 
      affiliations as standing_subgroup_affiliations,
     affiliations as season_affiliations,
     sub_seasons,
     seasons,
     teams
WHERE
         standing_affiliations.affiliation_key = 'l.mlb.com'
     AND seasons.season_key = '2008'
     AND seasons.league_id = standing_affiliations.id
     AND standing_affiliations.affiliation_type = 'league'
     AND sub_seasons.sub_season_type = 'season-regular'
     AND sub_seasons.season_id = seasons.id
     AND outcome_totals.standing_subgroup_id = standing_subgroups.id
      AND standing_subgroups.standing_id = standings.id
     AND standings.affiliation_id = standing_affiliations.id
     AND standings.sub_season_id = sub_seasons.id
     AND outcome_totals.outcome_holder_type = 'teams'
     AND outcome_totals.outcome_holder_id = teams.id
      AND standing_subgroups.affiliation_id = standing_subgroup_affiliations.id 
     AND seasons.league_id = season_affiliations.id
ORDER BY
     league_key,
     division_key,
     outcome_totals.rank,
     standing_subgroups.competition_scope,
     standing_subgroups.alignment_scope
DESCRIPTION Returns standings for all MLB teams sorted by division.
DBs TESTED MySQL
ADDITIONAL NOTES For standings from TSN, this query may return more than one row for each team: overall record and standings position, away record, home record and record vs. division.

You can narrow it down using the standing_subgroups.alignment_scope and standing_subgroups.competition_scope columns.
Comments
on April 24, 2009 5:16pm EDT Casey Trauer wrote:
Metadata: DB Tested: MS SQL
Query:
SELECT 
     standing_affiliations.affiliation_key as league_key,
     standing_subgroup_affiliations.affiliation_key as division_key,
     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 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

"This is a bit cleaner query, and adds games back and streak info."

on March 04, 2010 2:02pm EST 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 = '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

"This query adds in names of teams and divisions."