SportsDB Home
Documentation
Specification
Samples
Query Library
SportsCaster Web Service
Hosted on SourceForge
and licensed under
Creative Commons GNU GPL.
Query Library
| Ian Marsman submitted this query on: May 06, 2009 12:26pm EDT | |
| TITLE | Find out when stats are 'in' |
|---|---|
| SPORTS | ALL SPORTS |
| QUERY | SELECT document_fixtures.fixture_key, events.event_key, documents.doc_id FROM documents JOIN document_fixtures ON (document_fixtures.id = documents.document_fixture_id AND document_fixtures.publisher_id = documents.publisher_id) JOIN document_fixtures_events ON (document_fixtures_events.document_fixture_id = document_fixtures.id AND document_fixtures_events.latest_document_id = documents.id) JOIN events ON (events.id = document_fixtures_events.event_id AND events.publisher_id = documents.publisher_id) WHERE document_fixtures.fixture_key = 'event-stats' AND events.event_key = 'l.nba.com-2008-e.11519' LIMIT 100; |
| DESCRIPTION | Find out when final stats for an event are available. Sample query lists a specific event key but you can put your own there. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | Tested on MySQL but should work fine on all supported databases. |
Comments
on November 10, 2009 3:29pm EST Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT document_fixtures.fixture_key, events.event_key, documents.doc_id, documents.date_time
FROM documents
JOIN document_fixtures ON ( document_fixtures.id = documents.document_fixture_id
AND document_fixtures.publisher_id = documents.publisher_id )
JOIN document_fixtures_events ON ( document_fixtures_events.document_fixture_id = document_fixtures.id
AND document_fixtures_events.latest_document_id = documents.id )
JOIN EVENTS ON ( events.id = document_fixtures_events.event_id
AND events.publisher_id = documents.publisher_id )
WHERE document_fixtures.fixture_key
IN (
'event-stats', 'event-stats-composite'
)
AND events.event_key = 'l.nfl.com-2009-e.2858';
Query:
SELECT document_fixtures.fixture_key, events.event_key, documents.doc_id, documents.date_time
FROM documents
JOIN document_fixtures ON ( document_fixtures.id = documents.document_fixture_id
AND document_fixtures.publisher_id = documents.publisher_id )
JOIN document_fixtures_events ON ( document_fixtures_events.document_fixture_id = document_fixtures.id
AND document_fixtures_events.latest_document_id = documents.id )
JOIN EVENTS ON ( events.id = document_fixtures_events.event_id
AND events.publisher_id = documents.publisher_id )
WHERE document_fixtures.fixture_key
IN (
'event-stats', 'event-stats-composite'
)
AND events.event_key = 'l.nfl.com-2009-e.2858';
"Altered slightly for football to include two fixtures that make up NFL boxscores from The Sports Network."