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: January 30, 2009 6:22pm EST | |
| TITLE | Motor racing event schedule |
|---|---|
| SPORTS | Motor Racing |
| QUERY | SELECT dn_event.full_name as event_name, dn_site.full_name as site_name, sites.site_key as site_location, events.event_key, CONVERT_TZ(events.start_date_time, '+00:00', '-05:00') as start_time, events.event_status FROM events JOIN display_names dn_event ON dn_event.entity_id = events.id JOIN events_sub_seasons ess ON ess.event_id = events.id JOIN sub_seasons ss ON ess.sub_season_id = ss.id JOIN seasons s ON ss.season_id = s.id JOIN affiliations a ON s.league_id = a.id JOIN sites ON sites.id = events.site_id JOIN display_names dn_site ON sites.id = dn_site.entity_id WHERE dn_event.entity_type = 'events' AND a.affiliation_type = 'league' AND a.affiliation_key = 'l.nascar.com.1' AND ss.sub_season_key = '2009_season_regular' AND dn_site.entity_type = 'sites' ORDER BY start_time; |
| DESCRIPTION | Returns event schedule for NASCAR events. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | As of Jan. 2009, SportsDB does not yet store location names or course metadata. |
Comments
on February 10, 2011 12:19pm EST Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT dn_event.full_name as event_name, dn_site.full_name as site_name, CONCAT(track_length.value," ",track_units.value) as track_info, locations.city, locations.state, events.event_key, CONVERT_TZ(events.start_date_time, '+00:00', '-05:00') as start_time, events.event_status FROM events
JOIN display_names dn_event ON dn_event.entity_id = events.id
JOIN events_sub_seasons ess ON ess.event_id = events.id
JOIN sub_seasons ss ON ess.sub_season_id = ss.id
JOIN seasons s ON ss.season_id = s.id
JOIN affiliations a ON s.league_id = a.id
JOIN sites ON sites.id = events.site_id
JOIN locations ON sites.location_id = locations.id
LEFT JOIN sports_property as track_length ON (track_length.sports_property_type = 'sites' AND track_length.sports_property_id = sites.id AND track_length.formal_name = 'length')
LEFT JOIN sports_property as track_units ON (track_units.sports_property_type = 'sites' AND track_units.sports_property_id = sites.id AND track_units.formal_name = 'length-units')
JOIN display_names dn_site ON sites.id = dn_site.entity_id
WHERE dn_event.entity_type = 'events'
AND a.affiliation_type = 'league'
AND a.affiliation_key = 'l.nascar.com.1'
AND ss.sub_season_key = '2010_season_regular'
AND dn_site.entity_type = 'sites'
ORDER BY start_time;
Query:
SELECT dn_event.full_name as event_name, dn_site.full_name as site_name, CONCAT(track_length.value," ",track_units.value) as track_info, locations.city, locations.state, events.event_key, CONVERT_TZ(events.start_date_time, '+00:00', '-05:00') as start_time, events.event_status FROM events
JOIN display_names dn_event ON dn_event.entity_id = events.id
JOIN events_sub_seasons ess ON ess.event_id = events.id
JOIN sub_seasons ss ON ess.sub_season_id = ss.id
JOIN seasons s ON ss.season_id = s.id
JOIN affiliations a ON s.league_id = a.id
JOIN sites ON sites.id = events.site_id
JOIN locations ON sites.location_id = locations.id
LEFT JOIN sports_property as track_length ON (track_length.sports_property_type = 'sites' AND track_length.sports_property_id = sites.id AND track_length.formal_name = 'length')
LEFT JOIN sports_property as track_units ON (track_units.sports_property_type = 'sites' AND track_units.sports_property_id = sites.id AND track_units.formal_name = 'length-units')
JOIN display_names dn_site ON sites.id = dn_site.entity_id
WHERE dn_event.entity_type = 'events'
AND a.affiliation_type = 'league'
AND a.affiliation_key = 'l.nascar.com.1'
AND ss.sub_season_key = '2010_season_regular'
AND dn_site.entity_type = 'sites'
ORDER BY start_time;
"Support for location names and course metadata added for 2011."