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 |