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: August 13, 2008 6:24pm EDT | |
| TITLE | Find conference for league events |
|---|---|
| SPORTS | ALL SPORTS |
| QUERY | SELECT e.event_key, a2.affiliation_key FROM events e JOIN affiliations a ON (a.affiliation_type = 'league' AND a.affiliation_key='l.ncaa.org.mfoot') JOIN affiliation_phases ap ON (ap.ancestor_affiliation_id=a.id) JOIN affiliations a2 ON (a2.id=ap.affiliation_id AND a2.affiliation_type='conference') JOIN affiliations_events ae ON (ae.affiliation_id=a2.id) WHERE e.id=ae.event_id ORDER by e.event_key |
| DESCRIPTION | This query is meant to find all event keys and conference keys for events for a league. I have done a quick test of the query but have not fully tested it. |
| DBs TESTED | Postgres |
| ADDITIONAL NOTES | |
Comments
on August 13, 2008 6:26pm EDT Ian Marsman wrote:
Metadata: DB Tested: n/a
Query:
n/a
Query:
n/a
"Here's some sample output.
l.ncaa.org.mfoot-2008-e.20786, c.mid-american
l.ncaa.org.mfoot-2008-e.20791, c.mid-american
l.ncaa.org.mfoot-2008-e.21233, c.mid-american
l.ncaa.org.mfoot-2008-e.21249, c.mid-american
l.ncaa.org.mfoot-2008-e.21256, c.mid-american
l.ncaa.org.mfoot-2008-e.21272, c.mid-american
l.ncaa.org.mfoot-2008-e.21280, c.mid-american
l.ncaa.org.mfoot-2008-e.21285, c.mid-american
l.ncaa.org.mfoot-2008-e.21288, c.mid-american
l.ncaa.org.mfoot-2008-e.21292, c.mid-american
l.ncaa.org.mfoot-2008-e.21293, c.mid-american
l.ncaa.org.mfoot-2008-e.21294, c.mid-american"