Query Library

Casey Trauer submitted this query on: October 07, 2010 2:28pm EDT
TITLE Get game-by-game stats for one player for a season
SPORTS American Football
QUERY SELECT
     display_names.first_name,
     display_names.last_name,
     persons.person_key,
     s.season_key,
     events.event_key,
events.event_status,
     DATE(CONVERT_TZ(events.start_date_time, '+00:00', '-04:00')) as start_date,
     american_football_passing_stats.passes_completions,
     american_football_passing_stats.passes_attempts,
     american_football_passing_stats.passes_percentage,
     american_football_passing_stats.passes_yards_gross,
     american_football_passing_stats.passes_touchdowns,
     american_football_passing_stats.passes_interceptions,
     american_football_sacks_against_stats.sacks_against_total,
     american_football_sacks_against_stats.sacks_against_yards,
     american_football_rushing_stats.rushes_attempts,
     american_football_rushing_stats.rushes_yards,
     american_football_rushing_stats.rushes_touchdowns,
     american_football_passing_stats.receptions_yards,
     american_football_passing_stats.receptions_touchdowns,
    american_football_fumbles_stats.fumbles_committed,
     american_football_fumbles_stats.fumbles_lost

FROM events
JOIN publishers ON events.publisher_id = publishers.id
JOIN events_sub_seasons ess ON ess.event_id = events.id
JOIN sub_seasons ss ON ss.id = ess.sub_season_id 
JOIN seasons s ON s.id = ss.season_id
JOIN affiliations a ON (a.id = s.league_id AND a.affiliation_type = 'league')
LEFT JOIN stats AS passing_stats ON (passing_stats.stat_coverage_id = events.id AND passing_stats.stat_coverage_type = 'events' AND passing_stats.stat_repository_type = 'american_football_passing_stats' and passing_stats.context = 'event')
JOIN persons ON (persons.id = passing_stats.stat_holder_id)
LEFT JOIN american_football_passing_stats ON (passing_stats.stat_repository_id = american_football_passing_stats.id)
LEFT JOIN stats AS rushing_stats ON (rushing_stats.stat_coverage_id = events.id AND rushing_stats.stat_coverage_type = 'events' AND rushing_stats.stat_repository_type = 'american_football_rushing_stats' AND rushing_stats.context = 'event' AND rushing_stats.stat_holder_id = persons.id)
LEFT JOIN american_football_rushing_stats ON (rushing_stats.stat_repository_id = american_football_rushing_stats.id)
LEFT JOIN stats AS fumbles_stats ON (fumbles_stats.stat_coverage_id = events.id AND fumbles_stats.stat_coverage_type = 'events' AND fumbles_stats.stat_repository_type = 'american_football_fumbles_stats' AND fumbles_stats.context = 'event' AND fumbles_stats.stat_holder_id = persons.id)
LEFT JOIN american_football_fumbles_stats ON (fumbles_stats.stat_repository_id = american_football_fumbles_stats.id)
LEFT JOIN stats AS sacks_stats ON (sacks_stats.stat_coverage_id = events.id AND sacks_stats.stat_coverage_type = 'events' AND sacks_stats.stat_repository_type = 'american_football_sacks_against_stats' AND sacks_stats.context = 'event' AND sacks_stats.stat_holder_id = persons.id)
LEFT JOIN american_football_sacks_against_stats ON (sacks_stats.stat_repository_id = american_football_sacks_against_stats.id)
JOIN display_names ON (display_names.entity_id = persons.id AND display_names.entity_type = 'persons')

WHERE publishers.publisher_key = 'sportsnetwork.com'
AND a.affiliation_key = 'l.nfl.com'
AND s.season_key = '2010'
AND persons.person_key = 'l.nfl.com-p.22'

ORDER BY start_date;
DESCRIPTION Returns stats in several categories for a given player for all games that match the season key.
DBs TESTED MySQL
ADDITIONAL NOTES