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: July 21, 2009 10:16pm EDT | |
| TITLE | Get roster for one team |
|---|---|
| SPORTS | American Football, Baseball, Basketball, Ice Hockey |
| QUERY | SELECT dn.first_name, dn.last_name, p.person_key, pp.phase_status, pos.abbreviation, pp.uniform_number, pp.regular_position_depth, pp.height, pp.weight FROM persons p JOIN person_phases pp ON p.id = pp.person_id JOIN teams t ON pp.membership_id = t.id JOIN display_names dn ON dn.entity_id = p.id JOIN positions pos ON pos.id = pp.regular_position_id JOIN publishers pub ON pub.id = p.publisher_id WHERE pp.membership_type = 'teams' AND pp.phase_status = 'active' OR pp.phase_status = 'injured' AND dn.entity_type = 'persons' AND pub.publisher_key = 'sportsnetwork.com' AND t.team_key = 'l.mlb.com-t.11' |
| DESCRIPTION | Returns a roster list for one team. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |
Comments
on July 22, 2009 3:10pm EDT Casey Trauer wrote:
Metadata: DB Tested: n/a
Query:
SELECT dn.first_name, dn.last_name, p.person_key, pp.phase_status, pos.abbreviation, pp.uniform_number, pp.regular_position_depth, pp.height, pp.weight
FROM persons p
JOIN person_phases pp ON p.id = pp.person_id
JOIN teams t ON pp.membership_id = t.id
JOIN display_names dn ON dn.entity_id = p.id
JOIN positions pos ON pos.id = pp.regular_position_id
JOIN publishers pub ON pub.id = p.publisher_id
WHERE pp.membership_type = 'teams'
AND pp.phase_status != 'inactive'
AND dn.entity_type = 'persons'
AND pub.publisher_key = 'sportsnetwork.com'
AND t.team_key = 'l.mlb.com-t.11'
Query:
SELECT dn.first_name, dn.last_name, p.person_key, pp.phase_status, pos.abbreviation, pp.uniform_number, pp.regular_position_depth, pp.height, pp.weight
FROM persons p
JOIN person_phases pp ON p.id = pp.person_id
JOIN teams t ON pp.membership_id = t.id
JOIN display_names dn ON dn.entity_id = p.id
JOIN positions pos ON pos.id = pp.regular_position_id
JOIN publishers pub ON pub.id = p.publisher_id
WHERE pp.membership_type = 'teams'
AND pp.phase_status != 'inactive'
AND dn.entity_type = 'persons'
AND pub.publisher_key = 'sportsnetwork.com'
AND t.team_key = 'l.mlb.com-t.11'
"CORRECTION: The originally posted query will return too many rows because of improper implementation of AND/OR statements in the WHERE clause. This query will correct that."
on September 08, 2009 11:16pm EDT Ian Marsman wrote:
Metadata: DB Tested: MySQL, Postgres
Query:
SELECT dnt.full_name AS team_name, dn.first_name, dn.last_name,
p.person_key, pp.phase_status, pos.abbreviation,
pp.uniform_number, pp.regular_position_depth, pp.height,
pp.weight
FROM persons p
JOIN person_phases AS pp ON (p.id = pp.person_id)
JOIN teams AS t ON (pp.membership_id = t.id)
JOIN display_names AS dn ON (dn.entity_id = p.id AND dn.entity_type='persons')
JOIN display_names AS dnt ON (dnt.entity_id = t.id AND dnt.entity_type='teams')
JOIN positions AS pos ON (pos.id = pp.regular_position_id)
JOIN publishers AS pub ON (pub.id = p.publisher_id AND pub.publisher_key = 'sportsnetwork.com')
WHERE pp.membership_type = 'teams'
AND pp.phase_status = 'active'
OR pp.phase_status = 'injured'
AND t.team_key = 'l.ncaa.org.mfoot-t.603'
Query:
SELECT dnt.full_name AS team_name, dn.first_name, dn.last_name,
p.person_key, pp.phase_status, pos.abbreviation,
pp.uniform_number, pp.regular_position_depth, pp.height,
pp.weight
FROM persons p
JOIN person_phases AS pp ON (p.id = pp.person_id)
JOIN teams AS t ON (pp.membership_id = t.id)
JOIN display_names AS dn ON (dn.entity_id = p.id AND dn.entity_type='persons')
JOIN display_names AS dnt ON (dnt.entity_id = t.id AND dnt.entity_type='teams')
JOIN positions AS pos ON (pos.id = pp.regular_position_id)
JOIN publishers AS pub ON (pub.id = p.publisher_id AND pub.publisher_key = 'sportsnetwork.com')
WHERE pp.membership_type = 'teams'
AND pp.phase_status = 'active'
OR pp.phase_status = 'injured'
AND t.team_key = 'l.ncaa.org.mfoot-t.603'
"Slightly reworked to add team name"