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: June 17, 2009 8:36pm EDT | |
| TITLE | Get schedule and results for one team |
|---|---|
| SPORTS | ALL SPORTS |
| QUERY | SELECT dn_team.full_name as team, t1.team_key as team_key, pe_team.alignment, dn_opponent.full_name as opponent, t2.team_key as opponent_team_key, events.event_key, CONVERT_TZ(events.start_date_time, '+00:00', '-05:00') as start_time, events.event_status, pe_team.event_outcome as result FROM events JOIN participants_events pe_team ON pe_team.event_id = events.id JOIN participants_events pe_opponent ON pe_opponent.event_id = events.id JOIN display_names dn_team ON dn_team.entity_id = pe_team.participant_id JOIN display_names dn_opponent ON dn_opponent.entity_id = pe_opponent.participant_id JOIN teams t1 ON pe_team.participant_id = t1.id JOIN teams t2 ON pe_opponent.participant_id = t2.id JOIN events_sub_seasons ess ON ess.event_id = events.id JOIN sub_seasons ss ON ess.sub_season_id = ss.id JOIN seasons s ON ss.season_id = s.id JOIN affiliations a ON s.league_id = a.id WHERE pe_team.participant_type = 'teams' AND pe_opponent.participant_type = 'teams' AND pe_opponent.participant_id != pe_team.participant_id AND dn_team.entity_type = 'teams' AND dn_opponent.entity_type = 'teams' AND a.affiliation_type = 'league' AND a.affiliation_key = 'l.mlb.com' AND ss.sub_season_key = '2009_season_regular' AND t1.team_key = 'l.mlb.com-t.6' ORDER BY start_time; |
| DESCRIPTION | Returns a season schedule and results. You change the variables -- a.affiliation_key, ss.sub_season_key, t1.team_key -- to get the result you want. Scores are available in participants_events.score, so that can be added to the query as well. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |