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 23, 2010 7:28pm EST | |
| TITLE | Conference standings 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, 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 = 'conference' ORDER BY league_key, division_key, outcome_totals.rank + 1, standing_subgroups.competition_scope, standing_subgroups.alignment_scope ; |
| DESCRIPTION | This query retrieves standings data for NHL by conference rank. Each team will have several rows with different snapshots of data (conference record, league record, events away, events home, etc). The key distinction between divisional and conference standings is the following statement in the WHERE clauses: standings.standing_type = 'conference' |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |