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