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