Query Library

Casey Trauer submitted this query on: February 23, 2010 7:25pm EST
TITLE Conference standings for NBA
SPORTS Basketball
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.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.nba.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 NBA 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