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: March 31, 2008 4:31pm EDT | |
| TITLE | Get links to all documents associated with a specific event |
|---|---|
| SPORTS | ALL SPORTS |
| QUERY | SELECT events.event_key, documents.doc_id, document_fixtures.fixture_key, document_fixtures.name FROM document_fixtures_events, events, documents, document_fixtures WHERE events.event_key = 'l.mlb.com-2007-e.BOS20070616' AND document_fixtures_events.event_id = events.id AND document_fixtures_events.document_fixture_id = document_fixtures.id AND document_fixtures_events.latest_document_id = documents.id |
| DESCRIPTION | Use this query to figure out exactly what documents contain the information you need about a specfic event |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |
Comments
on January 20, 2010 10:57am EST Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT
events.event_key,
documents.doc_id,
document_fixtures.fixture_key,
document_fixtures.name,
document_contents.sportsml
FROM
document_fixtures_events, events, documents, document_fixtures,document_contents
WHERE
events.event_key = 'l.nhl.com-2009-e.20394'
AND document_fixtures_events.event_id = events.id
AND document_fixtures_events.document_fixture_id = document_fixtures.id
AND document_fixtures_events.latest_document_id = documents.id
AND document_contents.document_id = documents.id
Query:
SELECT
events.event_key,
documents.doc_id,
document_fixtures.fixture_key,
document_fixtures.name,
document_contents.sportsml
FROM
document_fixtures_events, events, documents, document_fixtures,document_contents
WHERE
events.event_key = 'l.nhl.com-2009-e.20394'
AND document_fixtures_events.event_id = events.id
AND document_fixtures_events.document_fixture_id = document_fixtures.id
AND document_fixtures_events.latest_document_id = documents.id
AND document_contents.document_id = documents.id
"This query adds a column that lists the path to each document in your archive folder of FeedFetcherDeluxe, in case you need to retrieve the XML file itself."
on September 19, 2011 8:25am EDT Casey Trauer wrote:
Metadata: DB Tested: MS SQL
Query:
SELECT
events.event_key,
documents.doc_id,
documents.db_loading_date_time,
document_fixtures.fixture_key,
document_fixtures.name,
document_contents.sportsml
FROM
document_fixtures_events, events, documents, document_fixtures,document_contents
WHERE
events.event_key = 'l.nfl.com-2011-e.3502'
AND document_fixtures_events.event_id = events.id
AND document_fixtures_events.document_fixture_id = document_fixtures.id
AND document_fixtures_events.latest_document_id = documents.id
AND document_contents.document_id = documents.id
ORDER BY documents.db_loading_date_time DESC
Query:
SELECT
events.event_key,
documents.doc_id,
documents.db_loading_date_time,
document_fixtures.fixture_key,
document_fixtures.name,
document_contents.sportsml
FROM
document_fixtures_events, events, documents, document_fixtures,document_contents
WHERE
events.event_key = 'l.nfl.com-2011-e.3502'
AND document_fixtures_events.event_id = events.id
AND document_fixtures_events.document_fixture_id = document_fixtures.id
AND document_fixtures_events.latest_document_id = documents.id
AND document_contents.document_id = documents.id
ORDER BY documents.db_loading_date_time DESC
"This orders the return by time loaded into the database, which can be useful for some diagnostics."