Query Library

Casey Trauer submitted this query on: February 16, 2011 12:32pm EST
TITLE NCAA Tournament schedule
SPORTS Basketball
QUERY SELECT events.event_key, CONVERT_TZ(events.start_date_time, '+00:00', '-04:00') as time, events.series_index as region, events.round_number as round, events.event_number, dn_home.full_name as home, rank_home.ranking_value as home_seed, dn_away.full_name as visitor, rank_away.ranking_value as visitor_seed 

FROM events
JOIN events_sub_seasons ess ON ess.event_id = events.id
JOIN sub_seasons ss ON ss.id = ess.sub_season_id
JOIN affiliations_events ae ON ae.event_id = events.id
JOIN affiliations a_league ON a_league.id = ae.affiliation_id
JOIN participants_events pe_home ON pe_home.event_id = events.id
JOIN participants_events pe_away ON pe_away.event_id = events.id
JOIN teams t ON t.id = pe_home.participant_id
JOIN display_names dn_home ON pe_home.participant_id = dn_home.entity_id
JOIN display_names dn_away ON pe_away.participant_id = dn_away.entity_id
JOIN rankings rank_home ON pe_home.participant_id = rank_home.participant_id
JOIN rankings rank_away ON pe_away.participant_id = rank_away.participant_id

WHERE ss.sub_season_key = '2010_post_season_ncaa'
AND a_league.affiliation_key = 'l.ncaa.org.mbasket'
AND a_league.affiliation_type = 'league'
AND pe_home.alignment = 'home'
AND pe_home.participant_type = 'teams'
AND pe_away.alignment = 'away'
AND pe_away.participant_type = 'teams'
AND dn_home.entity_type = 'teams'
AND dn_away.entity_type = 'teams'
GROUP BY events.event_key
ORDER BY events.start_date_time;
DESCRIPTION Describes NCAA Tournament games, including seeds for each team. Bracket position of game is described by three columns:

series_index: What region
round_number: What round
event_number: Game # for round determined by its vertical position in bracket
DBs TESTED MySQL
ADDITIONAL NOTES