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: October 27, 2010 4:16pm EDT | |
| TITLE | Basketball offensive stats for players for a game |
|---|---|
| SPORTS | Basketball |
| QUERY | SELECT dn_team.first_name, dn_player.first_name, dn_player.last_name, p.person_key, pos.abbreviation, pem.status, off.field_goals_made FG, off.field_goals_attempted FGA, off.three_pointers_made 3G, off.three_pointers_attempted 3GA, free_throws_made FT, free_throws_attempted FTA, off.points_scored_total PTS, off.assists_total AST, off.turnovers_total 'TO' FROM events AS e JOIN stats AS s ON (s.stat_coverage_type = 'events' AND s.stat_coverage_id=e.id) JOIN basketball_offensive_stats off ON (s.stat_repository_id = off.id AND s.stat_repository_type = 'basketball_offensive_stats') JOIN display_names dn_player ON (dn_player.entity_id = s.stat_holder_id AND dn_player.entity_type = 'persons' AND s.stat_holder_type = 'persons') JOIN display_names dn_team ON (dn_team.entity_id = s.stat_membership_id AND dn_team.entity_type = 'teams' AND s.stat_membership_type = 'teams') JOIN persons p ON s.stat_holder_id = p.id JOIN teams t ON s.stat_membership_id = t.id JOIN person_event_metadata pem ON (pem.event_id = e.id AND pem.person_id = s.stat_holder_id) JOIN participants_events pe ON (pe.event_id = e.id AND pe.participant_type = 'teams' AND pe.participant_id = s.stat_membership_id) JOIN positions pos ON pem.position_id = pos.id JOIN publishers pub ON e.publisher_id = pub.id WHERE pub.publisher_key = 'sportsnetwork.com' AND e.event_key = 'l.nba.com-2010-e.12984' AND s.context = 'event-play' /*Distinguishes between in-game stats ('event-play') and post-game stats ('event')*/ ORDER BY pe.alignment ASC, pem.status DESC, off.points_scored_total+1 DESC; |
| DESCRIPTION | This returns basketball offensive stats for in-game boxscores. If you want post-game boxes, change this clause: AND s.context = 'event-play' /*Distinguishes between in-game stats ('event-play') and post-game stats ('event')*/ |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |