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:08am EST | |
| TITLE | Get event-keys for all games today in a league |
|---|---|
| SPORTS | ALL SPORTS |
| QUERY | SELECT events.event_key, events.start_date_time FROM events, affiliations, affiliations_events WHERE affiliations_events.affiliation_id = affiliations.id AND affiliations_events.event_id = events.id AND affiliations.affiliation_key = 'l.mlb.com' AND events.start_date_time > '2007-08-04' AND events.start_date_time < '2007-08-05' |
| DESCRIPTION | gets event-keys |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |
Comments
on August 06, 2009 11:46am EDT Casey Trauer wrote:
Metadata: DB Tested: MS SQL, MySQL
Query:
SELECT events.*
FROM events
JOIN affiliations_events ae ON ae.event_id = events.id
JOIN affiliations a ON ae.affiliation_id = a.id
JOIN participants_events pe ON pe.event_id = events.id
JOIN teams t ON pe.participant_id = t.id
JOIN team_phases tp ON t.id = tp.team_id
JOIN affiliations a2 ON tp.affiliation_id = a2.id
JOIN publishers pub ON t.publisher_id = pub.id
WHERE pub.publisher_key = 'sportsnetwork.com'
AND a.affiliation_key = 'l.mlb.com'
AND pe.alignment = 'home'
AND pe.participant_type = 'teams'
AND a2.affiliation_type = 'conference'
AND a2.affiliation_key = 'c.national'
AND events.start_date_time > '2009-08-06 09:00:00'
AND events.start_date_time < '2009-08-07 09:00:00'
Query:
SELECT events.*
FROM events
JOIN affiliations_events ae ON ae.event_id = events.id
JOIN affiliations a ON ae.affiliation_id = a.id
JOIN participants_events pe ON pe.event_id = events.id
JOIN teams t ON pe.participant_id = t.id
JOIN team_phases tp ON t.id = tp.team_id
JOIN affiliations a2 ON tp.affiliation_id = a2.id
JOIN publishers pub ON t.publisher_id = pub.id
WHERE pub.publisher_key = 'sportsnetwork.com'
AND a.affiliation_key = 'l.mlb.com'
AND pe.alignment = 'home'
AND pe.participant_type = 'teams'
AND a2.affiliation_type = 'conference'
AND a2.affiliation_key = 'c.national'
AND events.start_date_time > '2009-08-06 09:00:00'
AND events.start_date_time < '2009-08-07 09:00:00'
"This query tweaks the original query a bit in that it returns only games where a National League team is the home team. You can change the a2.affiliation_key value to c.american to get AL-hosted games.
Also, the time query uses a time, acknowledging that values in the DB are GMT offset, so it will return games that have a value of 2009-08-07 in the DB but actually start 2009-08-06 local time. This is just one way to account for GMT offset."