SportsDB Home
Documentation
Specification
Samples
Query Library
SportsCaster Web Service
Hosted on SourceForge
and licensed under
Creative Commons GNU GPL.
Query Library
| Alan Karben submitted this query on: February 27, 2008 9:11pm EST | |
| TITLE | Get all players affiliated with a team |
|---|---|
| SPORTS | ALL SPORTS |
| QUERY | select persons.person_key, dn_person.full_name, dn_team.full_name, publishers.publisher_key, person_phases.phase_status from person_phases, persons, display_names as dn_person, teams, display_names as dn_team, publishers, affiliations, team_phases where 1 and affiliations.affiliation_key = 'l.mlb.com' and team_phases.affiliation_id = affiliations.id and team_phases.team_id = teams.id and person_phases.membership_type = 'teams' and person_phases.membership_id = teams.id and publishers.publisher_key = 'fantasysports.ca' and person_phases.person_id = persons.id and dn_person.entity_type = 'persons' and dn_person.entity_id = persons.id and dn_team.entity_type = 'teams' and dn_team.entity_id = teams.id and persons.publisher_id = publishers.id ; |
| DESCRIPTION | Searches person_phases to list out players affiliated with a team. Extra WHERE clause options such as person_phases.phase_status = 'active' would limit the list to active players. Can also trim things down with a teams.team_key = (someTeamKey) clause. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |