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 14, 2009 8:44pm EDT | |
| TITLE | Get MLB starting lineup |
|---|---|
| SPORTS | Baseball |
| QUERY | SELECT p.person_key, dn.first_name, dn.last_name, t.team_key, pos.abbreviation as position, pem.lineup_slot, pem.lineup_slot_sequence FROM person_event_metadata pem JOIN events e ON e.id = pem.event_id JOIN positions pos ON pem.position_id = pos.id JOIN persons p ON p.id = pem.person_id JOIN teams t ON pem.team_id = t.id JOIN display_names dn ON dn.entity_id = pem.person_id WHERE e.event_key = 'l.mlb.com-2009-e.26368' AND pem.lineup_slot_sequence = '1' AND dn.entity_type = 'persons' ORDER BY t.team_key, pem.lineup_slot, pem.lineup_slot_sequence; |
| DESCRIPTION | Returns a list of starters plus positions for each team in the order in which they bat. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | If you want to also retrieve substitute players, remove this clause: AND pem.lineup_slot_sequence = '1' |
Comments
on August 19, 2009 3:26pm EDT Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT p.person_key, dn.first_name, dn.last_name, t.team_key, pos.abbreviation as position, pem.status
FROM person_event_metadata pem
JOIN events e ON e.id = pem.event_id
JOIN positions pos ON pem.position_id = pos.id
JOIN persons p ON p.id = pem.person_id
JOIN teams t ON pem.team_id = t.id
JOIN display_names dn ON dn.entity_id = pem.person_id
WHERE e.event_key = 'l.mlb.com-2009-e.26368'
AND dn.entity_type = 'persons'
ORDER BY t.team_key, pem.lineup_slot;
Query:
SELECT p.person_key, dn.first_name, dn.last_name, t.team_key, pos.abbreviation as position, pem.status
FROM person_event_metadata pem
JOIN events e ON e.id = pem.event_id
JOIN positions pos ON pem.position_id = pos.id
JOIN persons p ON p.id = pem.person_id
JOIN teams t ON pem.team_id = t.id
JOIN display_names dn ON dn.entity_id = pem.person_id
WHERE e.event_key = 'l.mlb.com-2009-e.26368'
AND dn.entity_type = 'persons'
ORDER BY t.team_key, pem.lineup_slot;
"This returns a list of all players who participated in the game. pem.status indicates whether the player was a starter."