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. |
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
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
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."