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