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). |
Comments
on October 05, 2010 12:57pm EDT Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT plays.id, plays.score_attempt_type, plays.strength, plays.play_result, team_names.first_name, team_names.last_name, teams.team_key, event_states.period_value, event_states.period_time_elapsed, event_states.context, home_team.full_name, pe_home.score, away_team.full_name, pe_away.score
FROM ice_hockey_action_plays as plays
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 participants_events pe_home ON (e.id = pe_home.event_id AND pe_home.participant_type = 'teams' AND pe_home.alignment = 'home')
JOIN participants_events pe_away ON (e.id = pe_away.event_id AND pe_away.participant_type = 'teams' AND pe_away.alignment = 'away')
JOIN display_names as team_names ON (plays.team_id = team_names.entity_id AND team_names.entity_type = 'teams')
JOIN display_names as home_team ON (pe_home.participant_id = home_team.entity_id AND home_team.entity_type = 'teams')
JOIN display_names as away_team ON (pe_away.participant_id = away_team.entity_id AND away_team.entity_type = 'teams')
JOIN teams ON plays.team_id = teams.id
JOIN publishers pub ON (e.publisher_id = pub.id AND pub.publisher_key = 'sportsnetwork.com')
WHERE e.event_key = 'l.nhl.com-2010-e.12792'
AND plays.score_attempt_type IS NOT NULL
AND context='event-play'
GROUP BY event_states.period_value, event_states.period_time_elapsed
ORDER BY event_states.period_value, event_states.period_time_elapsed + 1 ASC
Query:
SELECT plays.id, plays.score_attempt_type, plays.strength, plays.play_result, team_names.first_name, team_names.last_name, teams.team_key, event_states.period_value, event_states.period_time_elapsed, event_states.context, home_team.full_name, pe_home.score, away_team.full_name, pe_away.score
FROM ice_hockey_action_plays as plays
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 participants_events pe_home ON (e.id = pe_home.event_id AND pe_home.participant_type = 'teams' AND pe_home.alignment = 'home')
JOIN participants_events pe_away ON (e.id = pe_away.event_id AND pe_away.participant_type = 'teams' AND pe_away.alignment = 'away')
JOIN display_names as team_names ON (plays.team_id = team_names.entity_id AND team_names.entity_type = 'teams')
JOIN display_names as home_team ON (pe_home.participant_id = home_team.entity_id AND home_team.entity_type = 'teams')
JOIN display_names as away_team ON (pe_away.participant_id = away_team.entity_id AND away_team.entity_type = 'teams')
JOIN teams ON plays.team_id = teams.id
JOIN publishers pub ON (e.publisher_id = pub.id AND pub.publisher_key = 'sportsnetwork.com')
WHERE e.event_key = 'l.nhl.com-2010-e.12792'
AND plays.score_attempt_type IS NOT NULL
AND context='event-play'
GROUP BY event_states.period_value, event_states.period_time_elapsed
ORDER BY event_states.period_value, event_states.period_time_elapsed + 1 ASC
"Returns scoring plays during the game, including current score (not the score at the time of the goal)."
on October 05, 2010 1:10pm EDT Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT plays.id, player_name.full_name, plays.score_attempt_type, plays.strength, plays.play_result, team_names.first_name, team_names.last_name, teams.team_key, event_states.period_value, event_states.period_time_elapsed, event_states.context, home_team.full_name, pe_home.score, away_team.full_name, pe_away.score
FROM ice_hockey_action_plays as plays
JOIN ice_hockey_action_participants players ON (players.ice_hockey_action_play_id = plays.id AND players.participant_role = 'scorer')
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 participants_events pe_home ON (e.id = pe_home.event_id AND pe_home.participant_type = 'teams' AND pe_home.alignment = 'home')
JOIN participants_events pe_away ON (e.id = pe_away.event_id AND pe_away.participant_type = 'teams' AND pe_away.alignment = 'away')
JOIN display_names as team_names ON (plays.team_id = team_names.entity_id AND team_names.entity_type = 'teams')
JOIN display_names as home_team ON (pe_home.participant_id = home_team.entity_id AND home_team.entity_type = 'teams')
JOIN display_names as away_team ON (pe_away.participant_id = away_team.entity_id AND away_team.entity_type = 'teams')
JOIN display_names as player_name ON (players.person_id = player_name.entity_id AND player_name.entity_type = 'persons')
JOIN teams ON plays.team_id = teams.id
JOIN publishers pub ON (e.publisher_id = pub.id AND pub.publisher_key = 'sportsnetwork.com')
WHERE e.event_key = 'l.nhl.com-2010-e.12792'
AND plays.score_attempt_type IS NOT NULL
AND context='event-play'
GROUP BY event_states.period_value, event_states.period_time_elapsed
ORDER BY event_states.period_value, event_states.period_time_elapsed + 1 ASC
Query:
SELECT plays.id, player_name.full_name, plays.score_attempt_type, plays.strength, plays.play_result, team_names.first_name, team_names.last_name, teams.team_key, event_states.period_value, event_states.period_time_elapsed, event_states.context, home_team.full_name, pe_home.score, away_team.full_name, pe_away.score
FROM ice_hockey_action_plays as plays
JOIN ice_hockey_action_participants players ON (players.ice_hockey_action_play_id = plays.id AND players.participant_role = 'scorer')
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 participants_events pe_home ON (e.id = pe_home.event_id AND pe_home.participant_type = 'teams' AND pe_home.alignment = 'home')
JOIN participants_events pe_away ON (e.id = pe_away.event_id AND pe_away.participant_type = 'teams' AND pe_away.alignment = 'away')
JOIN display_names as team_names ON (plays.team_id = team_names.entity_id AND team_names.entity_type = 'teams')
JOIN display_names as home_team ON (pe_home.participant_id = home_team.entity_id AND home_team.entity_type = 'teams')
JOIN display_names as away_team ON (pe_away.participant_id = away_team.entity_id AND away_team.entity_type = 'teams')
JOIN display_names as player_name ON (players.person_id = player_name.entity_id AND player_name.entity_type = 'persons')
JOIN teams ON plays.team_id = teams.id
JOIN publishers pub ON (e.publisher_id = pub.id AND pub.publisher_key = 'sportsnetwork.com')
WHERE e.event_key = 'l.nhl.com-2010-e.12792'
AND plays.score_attempt_type IS NOT NULL
AND context='event-play'
GROUP BY event_states.period_value, event_states.period_time_elapsed
ORDER BY event_states.period_value, event_states.period_time_elapsed + 1 ASC
"Same as 2nd query but adding in scorer name."