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.