Query Library

Casey Trauer submitted this query on: February 15, 2013 7:44pm EST
TITLE Basketball player fouls
SPORTS Basketball
QUERY SELECT dn_team.first_name, dn_team.last_name, t.team_key, dn_player.first_name, dn_player.last_name, p.person_key, pen.*
FROM events AS e
JOIN stats AS s ON (s.stat_coverage_type = 'events' AND s.stat_coverage_id=e.id AND s.stat_holder_type = 'persons')
JOIN penalty_stats pen ON (s.stat_repository_id = pen.id AND s.stat_repository_type = 'penalty_stats')
JOIN display_names dn_team ON (dn_team.entity_id = s.stat_membership_id AND dn_team.entity_type = 'teams')
JOIN display_names dn_player ON (dn_player.entity_id = s.stat_holder_id AND dn_player.entity_type = 'persons')
JOIN teams t ON s.stat_membership_id = t.id
JOIN persons p ON s.stat_holder_id = p.id
JOIN publishers pub ON e.publisher_id = pub.id

WHERE pub.publisher_key = 'sportsnetwork.com'
AND e.event_key = 'l.nba.com-2012-e.17705'
AND s.context = 'event'
DESCRIPTION Lists personal fouls for an event for each player on each team.
DBs TESTED MySQL
ADDITIONAL NOTES
Comments
on May 23, 2013 6:38am EDT Jkai Hsu wrote:
Metadata: DB Tested: MySQL
Query:
n/a

"query"

on October 26, 2015 10:57pm EDT David Cleveland wrote:
Metadata: DB Tested: MySQL
Query:
SELECT dn_team.first_name, dn_team.last_name, t.team_key, dn_player.first_name, dn_player.last_name, p.person_key, pen.*
FROM events AS e
JOIN stats AS s ON (s.stat_coverage_type = 'events' AND s.stat_coverage_id=e.id AND s.stat_holder_type = 'persons')
JOIN penalty_stats pen ON (s.stat_repository_id = pen.id AND s.stat_repository_type = 'penalty_stats')
JOIN display_names dn_team ON (dn_team.entity_id = s.stat_membership_id AND dn_team.entity_type = 'teams')
JOIN display_names dn_player ON (dn_player.entity_id = s.stat_holder_id AND dn_player.entity_type = 'persons')
JOIN teams t ON s.stat_membership_id = t.id
JOIN persons p ON s.stat_holder_id = p.id
JOIN publishers pub ON e.publisher_id = pub.id

"foo"