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:58pm EST | |
| TITLE | Motor racing results |
|---|---|
| SPORTS | Motor Racing |
| QUERY | SELECT pe.rank, dn.full_name, p.person_key, mres.lap, mres.laps_remaining FROM participants_events pe JOIN events e ON pe.event_id = e.id JOIN display_names dn ON pe.participant_id = dn.entity_id JOIN persons p ON pe.participant_id = p.id JOIN motor_racing_event_states mres ON mres.event_id = e.id WHERE e.event_key = 'l.nascar.com.nextel-2007-e.687' AND dn.entity_type = 'persons' ORDER BY pe.rank + 1; |
| DESCRIPTION | Returns a listing of race leaders and race status for a given event key. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | Data from the Sports Network contains the top 10 drivers mid-race and a full listing of drivers post-race. Post-race data has more information, such as points earned. |
Comments
on January 24, 2011 9:08pm EST Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT pe.rank, dn.full_name, p.person_key, mres.lap, mres.laps_remaining, make.value as make, car_number.value as car_number
FROM participants_events pe
JOIN events e ON pe.event_id = e.id
JOIN display_names dn ON pe.participant_id = dn.entity_id
JOIN persons p ON pe.participant_id = p.id
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_event_states mres ON mres.event_id = e.id
WHERE e.event_key = 'l.nascar.com.1-2010-e.1099'
AND dn.entity_type = 'persons'
ORDER BY pe.rank + 1;
Query:
SELECT pe.rank, dn.full_name, p.person_key, mres.lap, mres.laps_remaining, make.value as make, car_number.value as car_number
FROM participants_events pe
JOIN events e ON pe.event_id = e.id
JOIN display_names dn ON pe.participant_id = dn.entity_id
JOIN persons p ON pe.participant_id = p.id
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_event_states mres ON mres.event_id = e.id
WHERE e.event_key = 'l.nascar.com.1-2010-e.1099'
AND dn.entity_type = 'persons'
ORDER BY pe.rank + 1;
"This query adds a way to retrieve car make and number. It is not perfect: it currently will retrieve only the latest car model and number for that driver. Because it is a property of the driver and not the driver/event, that value will change regardless of event if the make or number changes."
on February 10, 2011 12:30pm EST Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT pe.rank, dn.full_name, p.person_key, mres.lap, mres.laps_remaining, mres.flag_state, make.value as make, car_number.value as car_number
FROM participants_events pe
JOIN events e ON pe.event_id = e.id
JOIN display_names dn ON pe.participant_id = dn.entity_id
JOIN persons p ON pe.participant_id = p.id
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_event_states mres ON mres.event_id = e.id
WHERE e.event_key = 'l.nascar.com.1-2010-e.1081'
AND dn.entity_type = 'persons'
ORDER BY pe.rank + 1;
Query:
SELECT pe.rank, dn.full_name, p.person_key, mres.lap, mres.laps_remaining, mres.flag_state, make.value as make, car_number.value as car_number
FROM participants_events pe
JOIN events e ON pe.event_id = e.id
JOIN display_names dn ON pe.participant_id = dn.entity_id
JOIN persons p ON pe.participant_id = p.id
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_event_states mres ON mres.event_id = e.id
WHERE e.event_key = 'l.nascar.com.1-2010-e.1081'
AND dn.entity_type = 'persons'
ORDER BY pe.rank + 1;
"Adds flag_state column, which usually has a value of green, yellow or checkered. New for 2011."