SportsDB Home
Documentation
Specification
Samples
Query Library
SportsCaster Web Service
Hosted on SourceForge
and licensed under
Creative Commons GNU GPL.
Query Library
| Chris Hartjes submitted this query on: April 01, 2008 2:37pm EDT | |
| TITLE | Get teams involved in a specifc event |
|---|---|
| SPORTS | American Football, Baseball, Basketball, Boxing, Ice Hockey, Soccer |
| QUERY | SELECT events.event_key, teams.team_key, display_names.full_name, participants_events.alignment, participants_events.score FROM EVENTS , participants_events, display_names, teams WHERE participants_events.event_id = events.id AND events.event_key = 'l.nhl.com-2007-e.8472' AND display_names.entity_id = participants_events.participant_id AND teams.id = participants_events.participant_id AND display_names.entity_type = 'teams' ORDER BY participants_events.alignment |
| DESCRIPTION | If you have the event key, you can pull out just the names of the two teams involved |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | Whenever you are joining a table using a *_id field, make sure you also do a join on the corresponding *_type field. In this case, because we're using display_names.entity_id we also need to look for display_names.entity_type |
Comments
on April 16, 2009 8:32pm EDT Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT events.event_key, teams.team_key, display_names.full_name, participants_events.alignment, participants_events.score
FROM events, participants_events, display_names, teams
WHERE participants_events.event_id = events.id
AND events.start_date_time LIKE '2009-04-16%'
AND events.event_key LIKE 'l.mlb.com%'
AND display_names.entity_id = participants_events.participant_id
AND teams.id = participants_events.participant_id
AND display_names.entity_type = 'teams'
AND participants_events.participant_type = 'teams'
ORDER BY participants_events.alignment
Query:
SELECT events.event_key, teams.team_key, display_names.full_name, participants_events.alignment, participants_events.score
FROM events, participants_events, display_names, teams
WHERE participants_events.event_id = events.id
AND events.start_date_time LIKE '2009-04-16%'
AND events.event_key LIKE 'l.mlb.com%'
AND display_names.entity_id = participants_events.participant_id
AND teams.id = participants_events.participant_id
AND display_names.entity_type = 'teams'
AND participants_events.participant_type = 'teams'
ORDER BY participants_events.alignment
"This is a slight variation on the query (looks for all events on a given date for a given league), but the key issue to note is that since participants_events can hold ids for players as well as teams, you need to specify which flavor of participant_events rows you want to join, or else you will get bad results for team key and name.
AND participants_events.participant_type = 'teams'"