SportsDB Home
Documentation
Specification
Samples
Query Library
SportsCaster Web Service
Hosted on SourceForge
and licensed under
Creative Commons GNU GPL.
Query Library
| Annonymous User submitted this query on: September 24, 2010 2:13pm EDT | |
| TITLE | Get overall standings for one NHL team |
|---|---|
| SPORTS | Ice Hockey |
| QUERY | SELECT standings.standing_type, standing_subgroups.alignment_scope, standing_subgroups.competition_scope, standing_subgroups.duration_scope, display_names_team.first_name, display_names_team.last_name, outcome_totals.rank, outcome_totals.events_played, outcome_totals.wins, outcome_totals.losses, outcome_totals.losses_overtime FROM standings JOIN standing_subgroups ON standing_subgroups.standing_id = standings.id JOIN affiliations ON affiliations.id = standing_subgroups.affiliation_id JOIN sub_seasons ON sub_seasons.id = standings.sub_season_id JOIN seasons ON seasons.id = sub_seasons.season_id JOIN outcome_totals ON outcome_totals.standing_subgroup_id = standing_subgroups.id JOIN teams ON teams.id = outcome_totals.outcome_holder_id JOIN display_names AS display_names_team ON display_names_team.entity_id = teams.id WHERE teams.team_key = 'l.nhl.com-t.1' AND seasons.season_key = '2009' AND display_names_team.entity_type = 'teams' ORDER BY outcome_totals.rank ASC |
| DESCRIPTION | This query will return all the associated rows related to one team in the standings. Each contains a different kind of record. If you want to return division rank, change standings.standing_type to 'division'. If you want to narrow down which rows you see (just overall record or just home record, etc), you will need to filter for specific values for alignment_scope (home, away, all), competition_scope (vs. division or league) and duration_scope (will have a value if it is a an overtime or shootout record) . |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |