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: April 20, 2009 7:19pm EDT | |
| TITLE | List affiliation_phase entries by conference, league, and division |
|---|---|
| SPORTS | ALL SPORTS |
| QUERY | SELECT dnr.full_name AS league, dn.full_name, aff.affiliation_type, aff.affiliation_key, aff.id AS affiliation_id FROM affiliations aff INNER JOIN publishers pub ON ( pub.publisher_key='sportsnetwork.com' ) INNER JOIN affiliation_phases ap ON ( ap.affiliation_id=aff.id AND ( ap.ancestor_affiliation_id=ap.root_id OR ap.affiliation_id=root_id )) INNER JOIN display_names dn ON ( dn.entity_type='affiliations' AND dn.entity_id=aff.id ) INNER JOIN affiliations afr ON ( afr.id = ap.root_id ) INNER JOIN display_names dnr ON ( dnr.entity_type='affiliations' AND dnr.entity_id=afr.id ) WHERE aff.publisher_id=pub.id ORDER by ap.id |
| DESCRIPTION | This example lists by the sportsnetwork.com publisher but that can be altered. The ordering relies on the league hierarchy having been traversed from highest to lowest level when the affiliation_phases records were created. FeedFetcher does it this way. |
| DBs TESTED | ALL DATABASES |
| ADDITIONAL NOTES | |