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: February 05, 2010 3:04pm EST | |
| TITLE | College basketball/football conference standings query |
|---|---|
| SPORTS | American Football, Basketball |
| QUERY | SELECT standing_affiliations.affiliation_key as league_key, standing_subgroup_affiliations.affiliation_key as division_key, teams.team_key, 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.games_back, outcome_totals.streak_type, outcome_totals.streak_total FROM standings INNER JOIN standing_subgroups ON standing_subgroups.standing_id = standings.id INNER JOIN outcome_totals ON outcome_totals.standing_subgroup_id = standing_subgroups.id INNER JOIN sub_seasons ON standings.sub_season_id = sub_seasons.id INNER JOIN seasons ON sub_seasons.season_id = seasons.id INNER JOIN affiliations as league_affiliations ON seasons.league_id = league_affiliations.id INNER JOIN affiliations as standing_affiliations ON standings.affiliation_id = standing_affiliations.id INNER JOIN affiliations as standing_subgroup_affiliations ON standing_subgroups.affiliation_id = standing_subgroup_affiliations.id INNER JOIN affiliations as season_affiliations ON seasons.league_id = season_affiliations.id INNER JOIN teams ON outcome_totals.outcome_holder_id = teams.id INNER JOIN publishers ON seasons.publisher_id = publishers.id WHERE league_affiliations.affiliation_key = 'l.ncaa.org.mbasket' AND standing_affiliations.affiliation_key = 'c.big-12' AND standing_affiliations.affiliation_type = 'conference' AND seasons.season_key = '2009' AND sub_seasons.sub_season_type = 'season-regular' AND outcome_totals.outcome_holder_type = 'teams' AND publishers.publisher_key = 'sportsnetwork.com' ORDER BY league_key, division_key, outcome_totals.rank, standing_subgroups.competition_scope, standing_subgroups.alignment_scope |
| DESCRIPTION | This query retrieves conference standings for college conferences where the standings are not divided up into divisions. (For conferences with divisions, you must be query for each division separately to retum the correct order.) This will return several rows for each team. One row will list league record, one row conference record and one row a scope that includes total games played stat. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |