SQL query, first, last?

Question

I have a database with three tables, one for a train, one for a location, and one for the list of locations called at by each train. I need to generate a list of trains based on a query, but within the results show the first and last location for each train.

I can do this with one query and dataset to return the list of trains, and then for each entry perform another query to get the first location for that train, and another for the last location. But there must be a better way.

Schedule:
- ID
- Name

Location
- ID
- Name

ScheduleLocation
- ScheduleID
- LocationID
- Sequence (ie 1 is the first location etc)

So the results should be:
(Schedule.Name [1]) - (Location.Name [first]) - (Location.Name [last])
(Schedule.Name [2]) - (Location.Name [first]) - (Location.Name [last])
...to...
(Schedule.Name [n]) - (Location.Name [first]) - (Location.Name [last])

The first query is simply "SELECT Schedule.ID, Schedule.Name FROM Schedule WHERE Schedule.ID LIKE (whatever)"

What I need is the rest of the query for the first and last locations. Thanks for any help!

TheMadProfessor · Accepted Answer

SELECT s.Name AS Schedule, fl.Name AS "First Location", ll.Name AS "Last Location" 
FROM Schedule s, Location fl, Location ll, ScheduleLocation fsl, ScheduleLocation lsl, 
(SELECT ScheduleID, MIN(Sequence) AS FirstSeq, MAX(Sequence) AS LastSeq 
FROM ScheduleLocation GROUP BY ScheduleID) mm
WHERE mm.ScheduleID = s.ScheduleID
AND mm.ScheduleID = fsl.ScheduleID AND mm.FirstSeq = fsl.Sequence
AND mm.ScheduleID = lsl.ScheduleID AND mm.LastSeq = lsl.Sequence
AND fsl.LocationID = fl.ID
AND lsl.LocationID = ll.ID

SQL query, first, last?

1 Answer