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: February 03, 2009 5:30pm 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, publishers 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 AND seasons.publisher_id = publishers.id 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 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. |
Comments
on February 03, 2009 5:24pm EST Casey Trauer wrote:
Metadata: DB Tested: n/a
Query:
n/a
Query:
n/a
"To get overall record, you currently would want to add the following clauses to WHERE:
AND standing_subgroups.duration_scope IS NULL
AND standing_subgroups.alignment_scope IS NULL
AND standing_subgroups.competition_scope = 'league'
The SportsDB database use to default to standing_subgroups.competition_scope = 'all' when no such scope was specified in the XML data. Current TSN standings now specify 'league', so that value gets populated."
on February 23, 2010 7:29pm EST Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT standing_affiliations.affiliation_key as league_key,
standing_subgroup_affiliations.affiliation_key as division_key,
teams.team_key,
display_names.full_name,
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
JOIN sub_seasons ON standings.sub_season_id = sub_seasons.id
JOIN seasons ON sub_seasons.season_id = seasons.id
JOIN affiliations as standing_affiliations ON standings.affiliation_id = standing_affiliations.id
JOIN standing_subgroups ON standing_subgroups.standing_id = standings.id
JOIN affiliations as standing_subgroup_affiliations ON standing_subgroups.affiliation_id = standing_subgroup_affiliations.id
JOIN outcome_totals ON outcome_totals.standing_subgroup_id = standing_subgroups.id
JOIN teams ON outcome_totals.outcome_holder_id = teams.id
JOIN display_names ON (display_names.entity_id = teams.id AND display_names.entity_type = 'teams')
JOIN publishers ON seasons.publisher_id = publishers.id
WHERE publishers.publisher_key = 'sportsnetwork.com'
AND sub_seasons.sub_season_type = 'season-regular'
AND seasons.season_key = '2009'
AND standing_affiliations.affiliation_key = 'l.nhl.com'
AND standings.standing_type = 'division'
ORDER BY
league_key,
division_key,
outcome_totals.rank + 1,
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,
display_names.full_name,
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
JOIN sub_seasons ON standings.sub_season_id = sub_seasons.id
JOIN seasons ON sub_seasons.season_id = seasons.id
JOIN affiliations as standing_affiliations ON standings.affiliation_id = standing_affiliations.id
JOIN standing_subgroups ON standing_subgroups.standing_id = standings.id
JOIN affiliations as standing_subgroup_affiliations ON standing_subgroups.affiliation_id = standing_subgroup_affiliations.id
JOIN outcome_totals ON outcome_totals.standing_subgroup_id = standing_subgroups.id
JOIN teams ON outcome_totals.outcome_holder_id = teams.id
JOIN display_names ON (display_names.entity_id = teams.id AND display_names.entity_type = 'teams')
JOIN publishers ON seasons.publisher_id = publishers.id
WHERE publishers.publisher_key = 'sportsnetwork.com'
AND sub_seasons.sub_season_type = 'season-regular'
AND seasons.season_key = '2009'
AND standing_affiliations.affiliation_key = 'l.nhl.com'
AND standings.standing_type = 'division'
ORDER BY
league_key,
division_key,
outcome_totals.rank + 1,
standing_subgroups.competition_scope,
standing_subgroups.alignment_scope;
"This query is a bit cleaner and clarifies how to retrieve division standings versus conference standings. The only difference is the ranking and the availability of conference/division records."