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 17, 2009 5:30pm EDT | |
| TITLE | Return a list of all NFL teams, conferences and divisions |
|---|---|
| SPORTS | American Football |
| QUERY | SELECT dn_league.full_name, a_league.affiliation_key, dn_conf.full_name, a_conf.affiliation_key, dn_div.full_name, a_div.affiliation_key, dn_team.full_name, t.team_key FROM teams t JOIN team_phases tp_div ON t.id = tp_div.team_id JOIN affiliations a_div ON tp_div.affiliation_id = a_div.id JOIN team_phases tp_conf ON t.id = tp_conf.team_id JOIN affiliations a_conf ON tp_conf.affiliation_id = a_conf.id JOIN team_phases tp_league ON t.id = tp_league.team_id JOIN affiliations a_league ON tp_league.affiliation_id = a_league.id JOIN display_names dn_team ON dn_team.entity_id = t.id JOIN display_names dn_div ON dn_div.entity_id = tp_div.affiliation_id JOIN display_names dn_conf ON dn_conf.entity_id = tp_conf.affiliation_id JOIN display_names dn_league ON dn_league.entity_id = tp_league.affiliation_id JOIN publishers pub ON pub.id = t.publisher_id WHERE a_div.affiliation_type = 'division' AND a_conf.affiliation_type = 'conference' AND a_league.affiliation_type = 'league' AND a_league.affiliation_key = 'l.nfl.com' AND dn_div.entity_type = 'affiliations' AND dn_conf.entity_type = 'affiliations' AND dn_league.entity_type = 'affiliations' AND dn_team.entity_type = 'teams' AND pub.publisher_key = 'sportsnetwork.com' ORDER BY dn_conf.full_name, dn_div.full_name, dn_team.full_name; |
| DESCRIPTION | Returns a list of teams. Must have loaded a league directory file in the database that establishes these affiliations. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |