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: February 06, 2009 2:30pm EST | |
| TITLE | Get all penalties for an NHL game |
|---|---|
| SPORTS | Ice Hockey |
| QUERY | SELECT event_states.sequence_number, plays.penalty_type, plays.penalty_length, players.participant_role, player_names.first_name, player_names.last_name, persons.person_key, team_names.first_name, team_names.last_name, teams.team_key, event_states.period_value, event_states.period_time_elapsed FROM ice_hockey_action_participants as players JOIN ice_hockey_action_plays as plays ON players.ice_hockey_action_play_id = plays.id JOIN ice_hockey_event_states as event_states ON plays.ice_hockey_event_state_id = event_states.id JOIN events e ON event_states.event_id = e.id JOIN display_names as player_names ON players.person_id = player_names.entity_id JOIN display_names as team_names ON plays.team_id = team_names.entity_id JOIN persons ON players.person_id = persons.id JOIN teams ON plays.team_id = teams.id WHERE e.event_key = 'l.nhl.com-2008-e.9907' AND player_names.entity_type = 'persons' AND team_names.entity_type = 'teams' AND plays.play_type = 'penalty' /* Filters for penalties */ ORDER BY event_states.sequence_number ASC |
| DESCRIPTION | Returns one row for each penalty. Designed for data from The Sports Network. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |