SportsDB Home
Documentation
Specification
Samples
Query Library
SportsCaster Web Service
Hosted on SourceForge
and licensed under
Creative Commons GNU GPL.
Query Library
| Alan Karben submitted this query on: February 07, 2008 1:16am EST | |
| TITLE | Get all teams and final scores for one event |
|---|---|
| SPORTS | ALL SPORTS |
| QUERY | SELECT * FROM events, participants_events WHERE events.event_key = 'l.mlb.com-2007-e.18640' AND participants_events.event_id = events.id |
| DESCRIPTION | final scores only... not sub-scores |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |
Comments
on December 03, 2009 2:38pm EST Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT dn.full_name, t.team_key, pe.score
FROM events as e
JOIN participants_events pe ON (pe.event_id = e.id AND pe.participant_type = 'teams')
JOIN display_names dn ON (dn.entity_id = pe.participant_id AND dn.entity_type = 'teams')
JOIN teams t ON pe.participant_id = t.id
WHERE e.event_key = 'l.nfl.com-2009-e.2979';
Query:
SELECT dn.full_name, t.team_key, pe.score
FROM events as e
JOIN participants_events pe ON (pe.event_id = e.id AND pe.participant_type = 'teams')
JOIN display_names dn ON (dn.entity_id = pe.participant_id AND dn.entity_type = 'teams')
JOIN teams t ON pe.participant_id = t.id
WHERE e.event_key = 'l.nfl.com-2009-e.2979';
"The first query needs to be modified because it also returns all the persons involved in the event. This query specifies we only want teams and also lists the team name."