SportsDB Home
Documentation
Specification
Samples
Query Library
SportsCaster Web Service
Hosted on SourceForge
and licensed under
Creative Commons GNU GPL.
Query Library
| Casey Trauer submitted this query on: August 21, 2009 4:46pm EDT | |
| TITLE | Find the most recent document for fixture key and league |
|---|---|
| SPORTS | ALL SPORTS |
| QUERY | SELECT d.* FROM documents d JOIN document_fixtures df ON d.document_fixture_id = df.id JOIN affiliations_documents ad ON d.id = ad.document_id JOIN affiliations a ON a.id = ad.affiliation_id JOIN publishers pub ON pub.id = d.publisher_id WHERE pub.publisher_key = 'sportsnetwork.com' AND a.affiliation_type = 'league' AND a.affiliation_key = 'l.mlb.com' AND df.fixture_key = 'weather' ORDER BY d.date_time DESC LIMIT 0,1; |
| DESCRIPTION | This returns info on the latest document for a given fixture key and league. Use it to find the last-update time for example. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |
Comments
on July 28, 2010 9:23am EDT Casey Trauer wrote:
Metadata: DB Tested: n/a
Query:
SELECT d.*, dc.sportsml
FROM documents d
JOIN document_fixtures df ON d.document_fixture_id = df.id
JOIN document_contents dc ON dc.document_id = d.id
JOIN affiliations_documents ad ON d.id = ad.document_id
JOIN affiliations a ON a.id = ad.affiliation_id
JOIN publishers pub ON pub.id = d.publisher_id
WHERE
pub.publisher_key = 'sportsnetwork.com'
AND a.affiliation_type = 'league'
AND a.affiliation_key = 'l.sportsml.com.general'
AND df.fixture_key = 'transactions'
ORDER BY d.date_time DESC
LIMIT 0,1;
Query:
SELECT d.*, dc.sportsml
FROM documents d
JOIN document_fixtures df ON d.document_fixture_id = df.id
JOIN document_contents dc ON dc.document_id = d.id
JOIN affiliations_documents ad ON d.id = ad.document_id
JOIN affiliations a ON a.id = ad.affiliation_id
JOIN publishers pub ON pub.id = d.publisher_id
WHERE
pub.publisher_key = 'sportsnetwork.com'
AND a.affiliation_type = 'league'
AND a.affiliation_key = 'l.sportsml.com.general'
AND df.fixture_key = 'transactions'
ORDER BY d.date_time DESC
LIMIT 0,1;
"Adds column that shows where the xml file is stored on the local server, usually relative to an archive folder."