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:43pm EST | |
| TITLE | Motor racing qualifying results and pole position |
|---|---|
| SPORTS | Motor Racing |
| QUERY | SELECT mrqs.qualifying_position, dn.full_name, p.person_key, mrqs.qualifying_time FROM motor_racing_qualifying_stats mrqs JOIN stats s ON s.stat_repository_id = mrqs.id JOIN events e ON s.stat_coverage_id = e.id JOIN display_names dn ON s.stat_holder_id = dn.entity_id JOIN persons p ON s.stat_holder_id = p.id WHERE e.event_key = 'l.nascar.com.nextel-2007-e.687' AND s.stat_repository_type = 'motor_racing_qualifying_stats' AND s.stat_coverage_type = 'events' AND dn.entity_type = 'persons' ORDER BY mrqs.qualifying_position + 1; |
| DESCRIPTION | Qualifying results for a NASCAR event. Requires event key. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |
Comments
on January 25, 2011 1:36pm EST Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
/** Qualifying results & starting grid **/
SELECT dn.full_name, p.person_key, mrqs.qualifying_position, mrqs.qualifying_time, make.value as make, car_number.value as car_number
FROM stats
JOIN events e ON (stats.stat_coverage_id = e.id AND stats.stat_coverage_type = 'events')
JOIN display_names dn ON (stats.stat_holder_id = dn.entity_id AND stats.stat_holder_type = 'persons')
JOIN persons p ON (stats.stat_holder_id = p.id AND stats.stat_holder_type = 'persons')
JOIN sports_property AS make ON (p.id = make.sports_property_id AND make.sports_property_type = 'persons' AND make.formal_name = 'make-name')
JOIN sports_property AS car_number ON (p.id = car_number.sports_property_id AND car_number.sports_property_type = 'persons' AND car_number.formal_name = 'number')
JOIN motor_racing_qualifying_stats mrqs ON (stats.stat_repository_id = mrqs.id AND stats.stat_repository_type = 'motor_racing_qualifying_stats')
WHERE e.event_key = 'l.nascar.com.1-2010-e.1099'
AND dn.entity_type = 'persons'
ORDER BY mrqs.qualifying_position + 1;
Query:
/** Qualifying results & starting grid **/
SELECT dn.full_name, p.person_key, mrqs.qualifying_position, mrqs.qualifying_time, make.value as make, car_number.value as car_number
FROM stats
JOIN events e ON (stats.stat_coverage_id = e.id AND stats.stat_coverage_type = 'events')
JOIN display_names dn ON (stats.stat_holder_id = dn.entity_id AND stats.stat_holder_type = 'persons')
JOIN persons p ON (stats.stat_holder_id = p.id AND stats.stat_holder_type = 'persons')
JOIN sports_property AS make ON (p.id = make.sports_property_id AND make.sports_property_type = 'persons' AND make.formal_name = 'make-name')
JOIN sports_property AS car_number ON (p.id = car_number.sports_property_id AND car_number.sports_property_type = 'persons' AND car_number.formal_name = 'number')
JOIN motor_racing_qualifying_stats mrqs ON (stats.stat_repository_id = mrqs.id AND stats.stat_repository_type = 'motor_racing_qualifying_stats')
WHERE e.event_key = 'l.nascar.com.1-2010-e.1099'
AND dn.entity_type = 'persons'
ORDER BY mrqs.qualifying_position + 1;
"Adds car make and number."