SportsDB Home
Documentation
Specification
Samples
Query Library
SportsCaster Web Service
Hosted on SourceForge
and licensed under
Creative Commons GNU GPL.
Query Library
| Alan Karben submitted this query on: March 23, 2008 11:33am EDT | |
| TITLE | Get all participants in a touchdown play for an american-football game |
|---|---|
| SPORTS | American Football |
| QUERY | SELECT display_names.first_name, display_names.last_name, american_football_action_participants.participant_role, american_football_action_participants.yards_gained, american_football_action_plays.comment, american_football_event_states.period_value, american_football_event_states.period_time_remaining from american_football_event_states, american_football_action_plays, american_football_action_participants, events, display_names where events.event_key like 'l.nfl.com-2007-e.2150' AND american_football_event_states.event_id = events.id AND american_football_action_plays.american_football_event_state_id = american_football_event_states.id AND american_football_action_participants.american_football_action_play_id = american_football_action_plays.id AND american_football_action_participants.person_id = display_names.entity_id AND display_names.entity_type = 'persons' AND american_football_action_plays.score_attempt_type = 'touchdown' ORDER BY american_football_event_states.sequence_number asc ; |
| DESCRIPTION | queries the action tables |
| DBs TESTED | MS SQL |
| ADDITIONAL NOTES | |
Comments
on December 03, 2009 2:50pm EST Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT
american_football_action_plays.id,
display_names.first_name,
display_names.last_name,
american_football_action_participants.participant_role,
american_football_action_participants.yards_gained,
american_football_action_plays.comment,
american_football_event_states.period_value,
american_football_event_states.period_time_remaining
from
american_football_event_states,
american_football_action_plays,
american_football_action_participants,
events,
display_names
where
events.event_key = 'l.nfl.com-2009-e.2979'
AND american_football_event_states.event_id = events.id
AND american_football_action_plays.american_football_event_state_id = american_football_event_states.id
AND american_football_action_participants.american_football_action_play_id = american_football_action_plays.id
AND american_football_action_participants.person_id = display_names.entity_id
AND display_names.entity_type = 'persons'
AND american_football_action_plays.score_attempt_type = 'touchdown'
AND american_football_event_states.context='event'
ORDER BY
american_football_event_states.sequence_number asc;
Query:
SELECT
american_football_action_plays.id,
display_names.first_name,
display_names.last_name,
american_football_action_participants.participant_role,
american_football_action_participants.yards_gained,
american_football_action_plays.comment,
american_football_event_states.period_value,
american_football_event_states.period_time_remaining
from
american_football_event_states,
american_football_action_plays,
american_football_action_participants,
events,
display_names
where
events.event_key = 'l.nfl.com-2009-e.2979'
AND american_football_event_states.event_id = events.id
AND american_football_action_plays.american_football_event_state_id = american_football_event_states.id
AND american_football_action_participants.american_football_action_play_id = american_football_action_plays.id
AND american_football_action_participants.person_id = display_names.entity_id
AND display_names.entity_type = 'persons'
AND american_football_action_plays.score_attempt_type = 'touchdown'
AND american_football_event_states.context='event'
ORDER BY
american_football_event_states.sequence_number asc;
"This modification does two things: lists the american_football_action_plays.id so that each unique scoring play can be identified (you could also use american_football_event_states.sequence_number).
Also, american_football_event_states.context='event' specifies that we only want data from the post-game boxscore. In The Sports Network feed, in-game boxscore data (context='event-play') gets processed separately than post-game boxscore data (context='event')."