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: October 26, 2009 4:03pm EDT | |
| TITLE | College football conference/division standings |
|---|---|
| SPORTS | American Football |
| QUERY | SELECT display_names_team.first_name, display_names_team.last_name, outcome_totals.rank, outcome_totals.wins, outcome_totals.losses, outcome_totals.winning_percentage, outcome_totals.points_scored_for, outcome_totals.points_scored_against FROM standings JOIN standing_subgroups ON standing_subgroups.standing_id = standings.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 IN (SELECT t.team_key FROM teams t JOIN team_phases tp ON t.id = tp.team_id JOIN affiliations a_division ON (tp.affiliation_id = a_division.id AND a_division.affiliation_key = 'd.north' AND a_division.affiliation_type = 'division') /* SELECT DIVISION */ JOIN affiliation_phases ap ON a_division.id = ap.affiliation_id JOIN affiliations a_conference ON (a_conference.id = ap.ancestor_affiliation_id AND a_conference.affiliation_key = 'c.big-12' AND a_conference.affiliation_type = 'conference') /* SELECT CONFERENCE */ JOIN affiliation_phases ap2 ON a_conference.id = ap2.affiliation_id JOIN affiliations a_league ON (a_league.id = ap2.ancestor_affiliation_id AND a_league.affiliation_key = 'l.ncaa.org.mfoot' AND a_league.affiliation_type = 'league') /* FILTER FOR LEAGUE/SPORT */) AND standing_subgroups.competition_scope = 'league' AND seasons.season_key = '2009' AND display_names_team.entity_type = 'teams' ORDER BY outcome_totals.rank ASC |
| DESCRIPTION | Some college football conferences have divisions and some do not. This variance makes having one standard query difficult. Here is a sample query to get the Big 12 North Division standings. Here is what you need to know to configure this query properly: - This query is specific to Sports Network standings data and requires a league directory file from XML Team be loaded into the database to establish conference and division affiliations for each team. - It contains a subquery to get all team keys for a division in a conference. It could be altered to just get all team keys for every team in a conference if it has no divisions. - Two types of records are stored: overall and conference record (not division record). Which type of record you retrieve depends on the value of standing_subgroups.competition_scope in the WHERE clause. That value will be league (overall) or conference (conference). |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |