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: November 04, 2008 12:35pm EST | |
| TITLE | divisional standings query for NHL |
|---|---|
| SPORTS | Ice Hockey |
| 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.ties as 'OTLosses', outcome_totals.winning_percentage, outcome_totals.events_played, outcome_totals.standing_points, outcome_totals.points_scored_for, outcome_totals.points_scored_against 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.nhl.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 | This query returns standings data for the National Hockey League, sorted by division. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | If this query is run against TSN data, it will return multiple rows for each team. Multiple outcome-totals rows are created based on the context: overall record, away/home record, OT record and various combinations of each. The context of each outcome-total row is defined by the following columns in standing_subgroups: alignment_scope (home/away), competition_scope (all/league/division) and duration_scope (OT). A column will be null if no context applies for that scope. You may choose to narrow down which records you want to return based on these scoping attributes. But not all outcome_totals columns (e.g. points_scored_for) contain data for each context. |