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:28pm EST | |
| TITLE | Get all scoring plays and attempts for an NHL game |
|---|---|
| SPORTS | Ice Hockey |
| QUERY | SELECT event_states.sequence_number, plays.score_attempt_type, plays.play_result, 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.score_attempt_type IS NOT NULL /* Filters out non scores and score attempts such as penalties */ ORDER BY event_states.sequence_number ASC |
| DESCRIPTION | Returns one row for each goal and assist scorer. Also returns one row each for shooter and goalie for each shootout attempt or penalty shot. Designed for feed data from The Sports Network. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | Score_attempt_type vocab for The Sports Network feed = regular, power-play, short-handed, penalty-shot, shootout. Note that scoring events send mid-game may have a different value (event-play) for ice_hockey_event_states.context than for post-game boxscores (event). |