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: August 17, 2009 5:06pm EDT | |
| TITLE | Get NFL schedule for week |
|---|---|
| SPORTS | American Football |
| QUERY | SELECT events.event_key, CONVERT_TZ(events.start_date_time, '+00:00', '-05:00') as time, dn_home.full_name as home, dn_away.full_name as visitor 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 WHERE ss.sub_season_key = '2009_season_regular' AND a_league.affiliation_key = 'l.nfl.com' 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' AND CONVERT_TZ(events.start_date_time, '+00:00', '-05:00') > '2009-09-08' AND CONVERT_TZ(events.start_date_time, '+00:00', '-05:00') < '2009-09-15' ORDER BY events.start_date_time;j |
| DESCRIPTION | Returns event-key, start time and team names for NFL schedule for a given week. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |
Comments
on August 17, 2009 5:07pm EDT Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT events.event_key, CONVERT_TZ(events.start_date_time, '+00:00', '-05:00') as time, dn_home.full_name as home, dn_away.full_name as visitor
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 team_phases tp ON tp.team_id = t.id
JOIN affiliations a_conference ON tp.affiliation_id = a_conference.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
WHERE ss.sub_season_key = '2009_season_regular'
AND a_league.affiliation_key = 'l.nfl.com'
AND a_league.affiliation_type = 'league'
AND a_conference.affiliation_key = 'c.afc'
AND a_conference.affiliation_type = 'conference'
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'
AND CONVERT_TZ(events.start_date_time, '+00:00', '-05:00') > '2009-09-08'
AND CONVERT_TZ(events.start_date_time, '+00:00', '-05:00') < '2009-09-15'
ORDER BY events.start_date_time;
Query:
SELECT events.event_key, CONVERT_TZ(events.start_date_time, '+00:00', '-05:00') as time, dn_home.full_name as home, dn_away.full_name as visitor
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 team_phases tp ON tp.team_id = t.id
JOIN affiliations a_conference ON tp.affiliation_id = a_conference.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
WHERE ss.sub_season_key = '2009_season_regular'
AND a_league.affiliation_key = 'l.nfl.com'
AND a_league.affiliation_type = 'league'
AND a_conference.affiliation_key = 'c.afc'
AND a_conference.affiliation_type = 'conference'
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'
AND CONVERT_TZ(events.start_date_time, '+00:00', '-05:00') > '2009-09-08'
AND CONVERT_TZ(events.start_date_time, '+00:00', '-05:00') < '2009-09-15'
ORDER BY events.start_date_time;
"This modifies the original query by returning only the games where the home team belongs to a given conference. (a_conference.affiliation_key = 'c.afc')."