The Sports Standards Alliance

Query Library

Casey Trauer submitted this query on: September 28, 2010 3:13pm EDT
TITLE	Return injury information for a team
SPORTS	ALL SPORTS
QUERY	SELECT dn_team.abbreviation, dn_team.full_name, t.team_key, dn_player.full_name, p.person_key, ip.injury_type, ip.injury_comment, ip.phase_type FROM injury_phases ip JOIN persons p ON ip.person_id = p.id JOIN publishers pub ON p.publisher_id = pub.id JOIN person_phases pp ON (pp.person_id = p.id AND pp.phase_status != 'inactive') JOIN teams t ON (pp.membership_id = t.id and pp.membership_type = 'teams') JOIN display_names dn_player ON (dn_player.entity_type = 'persons' AND dn_player.entity_id = p.id) JOIN display_names dn_team ON (dn_team.entity_type = 'teams' AND dn_team.entity_id = t.id) WHERE ip.injury_status = 'active' /* Shows only current injury data. */ AND t.team_key = 'l.nhl.com-t.22' AND pub.publisher_key='sportsnetwork.com';
DESCRIPTION	This query returns current injury information for a given team. You will need to have processed rosters to have established the link between the players and the teams they play on.
DBs TESTED	MySQL
ADDITIONAL NOTES	You can also return historical injury information by removing ip.injury_status = 'active'. The injury_phases columns start_date_time and end_date_time represent the timestamps of the files when the injury was first reported and then removed from the injury file.