Query Library

Casey Trauer submitted this query on: January 27, 2011 4:18pm EST
TITLE NCAA Tournament seeds list
SPORTS Basketball
QUERY SELECT dn.first_name, dn.last_name, t.team_key, rankings.ranking_value as seed, rankings.ranking_type as region 

FROM rankings
JOIN sub_seasons ss1 ON rankings.date_coverage_id = ss1.id
JOIN seasons sea ON ss1.season_id = sea.id
JOIN affiliations a ON sea.league_id = a.id
JOIN teams t ON rankings.participant_id = t.id
JOIN display_names dn ON (dn.entity_type = 'teams' AND dn.entity_id = rankings.participant_id)


WHERE ss1.sub_season_key = '2009_post_season_ncaa'
AND rankings.issuer = 'NCAA'
AND a.affiliation_type = 'league'
AND a.affiliation_key = 'l.ncaa.org.mbasket'

ORDER BY region ASC, seed + 1 ASC;
DESCRIPTION Returns a list of seeds for the NCAA Tournament.
DBs TESTED MS SQL
ADDITIONAL NOTES
Comments
on January 27, 2011 4:19pm EST Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT dn.first_name, dn.last_name, t.team_key, rankings.ranking_value as seed, rankings.ranking_type as region, outcome_totals.wins, outcome_totals.losses

FROM rankings
JOIN sub_seasons ss1 ON rankings.date_coverage_id = ss1.id
JOIN seasons sea ON ss1.season_id = sea.id
JOIN affiliations a ON sea.league_id = a.id
JOIN teams t ON rankings.participant_id = t.id
JOIN display_names dn ON (dn.entity_type = 'teams' AND dn.entity_id = rankings.participant_id)
JOIN outcome_totals ON rankings.participant_id = outcome_totals.outcome_holder_id
JOIN standing_subgroups ON outcome_totals.standing_subgroup_id = standing_subgroups.id
JOIN standings ON standing_subgroups.standing_id = standings.id
JOIN sub_seasons ss2 ON standings.sub_season_id = ss2.id

WHERE ss1.sub_season_key = '2009_post_season_ncaa'
AND rankings.issuer = 'NCAA'
AND a.affiliation_type = 'league'
AND a.affiliation_key = 'l.ncaa.org.mbasket'
AND standing_subgroups.competition_scope = 'league'
AND ss2.sub_season_key = '2009_season_regular'

ORDER BY region ASC, seed + 1 ASC;

"Returns a list of seeds, plus final season record, for teams in the NCAA Tournament."