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: July 10, 2009 10:26am EDT | |
| TITLE | Season offensive stats for all players on one MLB team |
|---|---|
| SPORTS | Baseball |
| QUERY | SELECT dn_player.first_name, dn_player.last_name, p.person_key, dn_team.full_name, t.team_key, off.* FROM stats s JOIN baseball_offensive_stats off ON s.stat_repository_id = off.id JOIN teams t ON s.stat_membership_id = t.id JOIN persons p ON s.stat_holder_id = p.id JOIN sub_seasons ss ON s.stat_coverage_id = ss.id JOIN display_names dn_player ON s.stat_holder_id = dn_player.entity_id JOIN display_names dn_team ON s.stat_membership_id = dn_team.entity_id WHERE s.stat_holder_type = 'persons' AND s.stat_coverage_type = 'sub_seasons' AND ss.sub_season_key = '2009_season_regular' AND s.stat_repository_type = 'baseball_offensive_stats' AND dn_player.entity_type = 'persons' AND dn_team.entity_type = 'teams' AND t.team_key = 'l.mlb.com-t.1'; |
| DESCRIPTION | Returns regular season offensive stats for all players on one MLB team. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | This query returns every column in the baseball_offensive_stats table (off.*), but be aware that the source data may not populate every column, so you may see NULL values in some fields. |
Comments
on June 14, 2010 5:15pm EDT Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT dn_player.first_name, dn_player.last_name, p.person_key, dn_team.full_name, t.team_key, core.events_played, off.*
FROM persons p
JOIN stats s1 ON (s1.stat_holder_id = p.id AND s1.stat_repository_type = 'baseball_offensive_stats')
JOIN stats s2 ON (s2.stat_holder_id = p.id AND s2.stat_repository_type = 'core_stats')
JOIN baseball_offensive_stats off ON s1.stat_repository_id = off.id
JOIN core_stats core ON s2.stat_repository_id = core.id
JOIN teams t ON s1.stat_membership_id = t.id
JOIN sub_seasons ss ON s1.stat_coverage_id = ss.id
JOIN display_names dn_player ON s1.stat_holder_id = dn_player.entity_id
JOIN display_names dn_team ON s1.stat_membership_id = dn_team.entity_id
WHERE s1.stat_holder_type = 'persons'
AND s1.stat_coverage_type = 'sub_seasons'
AND ss.sub_season_key = '2010_season_regular'
AND dn_player.entity_type = 'persons'
AND dn_team.entity_type = 'teams'
AND s1.stat_holder_type = s2.stat_holder_type
AND s1.stat_coverage_type = s2.stat_coverage_type
AND s1.stat_coverage_id = s2.stat_coverage_id
AND t.team_key = 'l.mlb.com-t.1';
Query:
SELECT dn_player.first_name, dn_player.last_name, p.person_key, dn_team.full_name, t.team_key, core.events_played, off.*
FROM persons p
JOIN stats s1 ON (s1.stat_holder_id = p.id AND s1.stat_repository_type = 'baseball_offensive_stats')
JOIN stats s2 ON (s2.stat_holder_id = p.id AND s2.stat_repository_type = 'core_stats')
JOIN baseball_offensive_stats off ON s1.stat_repository_id = off.id
JOIN core_stats core ON s2.stat_repository_id = core.id
JOIN teams t ON s1.stat_membership_id = t.id
JOIN sub_seasons ss ON s1.stat_coverage_id = ss.id
JOIN display_names dn_player ON s1.stat_holder_id = dn_player.entity_id
JOIN display_names dn_team ON s1.stat_membership_id = dn_team.entity_id
WHERE s1.stat_holder_type = 'persons'
AND s1.stat_coverage_type = 'sub_seasons'
AND ss.sub_season_key = '2010_season_regular'
AND dn_player.entity_type = 'persons'
AND dn_team.entity_type = 'teams'
AND s1.stat_holder_type = s2.stat_holder_type
AND s1.stat_coverage_type = s2.stat_coverage_type
AND s1.stat_coverage_id = s2.stat_coverage_id
AND t.team_key = 'l.mlb.com-t.1';
"Adds in events_played to query. Shows show you can join the stats table twice to retrieve data from multiple stat tables for each player."