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: June 01, 2009 6:34pm EDT | |
| TITLE | Get starting pitchers for game |
|---|---|
| SPORTS | Baseball |
| QUERY | SELECT p.person_key, dn.first_name, dn.last_name, t.team_key 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.24780' AND pem.status = 'starter' AND pos.abbreviation = '1' AND dn.entity_type = 'persons'; |
| DESCRIPTION | Retrieves starting pitcher names and keys. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |
Comments
on April 07, 2010 11:30pm EDT Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT p.person_key, dn.first_name, dn.last_name, t.team_key
FROM person_event_metadata pem
JOIN events as 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-2010-e.27413'
AND pos.abbreviation = '1'
AND pem.status = 'starter'
AND dn.entity_type = 'persons'
AND pem.id = (SELECT MAX(pem2.id)
FROM person_event_metadata pem2
WHERE pem.team_id = pem2.team_id
AND pem.event_id = pem2.event_id);
Query:
SELECT p.person_key, dn.first_name, dn.last_name, t.team_key
FROM person_event_metadata pem
JOIN events as 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-2010-e.27413'
AND pos.abbreviation = '1'
AND pem.status = 'starter'
AND dn.entity_type = 'persons'
AND pem.id = (SELECT MAX(pem2.id)
FROM person_event_metadata pem2
WHERE pem.team_id = pem2.team_id
AND pem.event_id = pem2.event_id);
"Sometimes SportsDB may have more than one row in person_event_metadata for a team because the starting pitcher has changed. If you want to retrieve only the latest row for each team, this query will return it to you and you will get only two rows, one for each team."