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:17am EST | |
| TITLE | Get all subscores for an event |
|---|---|
| SPORTS | ALL SPORTS |
| QUERY | SELECT * FROM events, participants_events, periods WHERE events.event_key = 'l.mlb.com-2007-e.18640' AND participants_events.event_id = events.id AND periods.participant_event_id = participants_events.id |
| DESCRIPTION | gets subscores |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |
Comments
on March 12, 2008 12:19pm EDT Alan Karben wrote:
Metadata: DB Tested: ALL DATABASES
Query:
SELECT
events.event_key,
teams.team_key,
display_names.full_name,
participants_events.alignment,
periods.period_value,
periods.score
FROM
events, participants_events, display_names, periods, teams
WHERE
participants_events.event_id = events.id
AND periods.participant_event_id = participants_events.id
AND events.event_key like 'l.mlb.com-2007-e.20131'
AND display_names.entity_id = participants_events.participant_id
AND display_names.entity_type = 'teams'
AND teams.id = participants_events.participant_id
ORDER BY
participants_events.alignment, periods.period_value
Query:
SELECT
events.event_key,
teams.team_key,
display_names.full_name,
participants_events.alignment,
periods.period_value,
periods.score
FROM
events, participants_events, display_names, periods, teams
WHERE
participants_events.event_id = events.id
AND periods.participant_event_id = participants_events.id
AND events.event_key like 'l.mlb.com-2007-e.20131'
AND display_names.entity_id = participants_events.participant_id
AND display_names.entity_type = 'teams'
AND teams.id = participants_events.participant_id
ORDER BY
participants_events.alignment, periods.period_value
"Here's a fancier version."