SportsDB Home
Documentation
Specification
Samples
Query Library
SportsCaster Web Service
Hosted on SourceForge
and licensed under
Creative Commons GNU GPL.
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. |