Excluding rows where the max value in one column is used to check the value in another?

Sorry for the obscure title, but I'm not sure how to sum it up.

I'm working with some static train schedule data supplied to me in several tables. I'm trying to show all the trains that stop at a specific station excluding those that end at the specified station. So for example, when listing all the trains that stop at NYPenn station, I don't want those trains terminating in NYPenn station.

The relevant tables are:

trips - list all of the trips made each day. each trip has a trip_id and consists of one or more stops. it also contains a trip_headsign column that shows the final destination of the train, but as text (not ID).

+----------+------------+---------+-------------------------+--------------+----------+----------+
| route_id | service_id | trip_id | trip_headsign           | direction_id | block_id | shape_id |
+----------+------------+---------+-------------------------+--------------+----------+----------+
|        1 |          1 |       1 |  PRINCETON RAIL SHUTTLE |            1 |      603 |   开发者_StackOverflow中文版     1 | 
|        1 |          2 |       2 |  PRINCETON RAIL SHUTTLE |            1 |      603 |        2 | 
+----------+------------+---------+-------------------------+--------------+----------+----------+

stop_times - lists every stop made by every train. all stops made on the same trip share a trip_id, so this is what i LEFT JOIN on. this table also has a column called stop_sequence, ranging from 1 to n, where n is the total number of stops for that trip. The train originates at stop_sequence=1. This value ranges from 2 to 26.

+---------+--------------+----------------+---------+---------------+-------------+---------------+---------------------+
| trip_id | arrival_time | departure_time | stop_id | stop_sequence | pickup_type | drop_off_type | shape_dist_traveled |
+---------+--------------+----------------+---------+---------------+-------------+---------------+---------------------+
|       1 | 21:15:00     | 21:15:00       |   24070 |             1 |           0 |             0 |                   0 | 
|       1 | 21:25:00     | 21:25:00       |   41586 |             2 |           0 |             0 |              2.5727 | 
+---------+--------------+----------------+---------+---------------+-------------+---------------+---------------------+

This particular train makes only two stops. The final stop (41586) is what's listed in the headsign column (notice it doesn't match the stop_name).

+---------------+---------+---------+-------------------------+----------+----------------+
| stop_sequence | stop_id | trip_id | trip_headsign           | block_id | departure_time |
+---------------+---------+---------+-------------------------+----------+----------------+
|             1 |   24070 |       1 |  PRINCETON RAIL SHUTTLE |      603 | 21:15:00       | 
|             2 |   41586 |       1 |  PRINCETON RAIL SHUTTLE |      603 | 21:25:00       | 
+---------------+---------+---------+-------------------------+----------+----------------+

+---------+----------------------------+-----------+-----------+------------+---------+
| stop_id | stop_name                  | stop_desc | stop_lat  | stop_lon   | zone_id |
+---------+----------------------------+-----------+-----------+------------+---------+
|   41586 | PRINCETON RAILROAD STATION |           | 40.343398 | -74.659872 |     336 | 
+---------+----------------------------+-----------+-----------+------------+---------+

So, again, what I'm looking to do is show a list of all the trains that stop at a particular station EXCEPT those that terminate at the station in question. The query I wrote to do this is (in this case, for stop_id 105 which is NY Penn station):

select stop_sequence, trips.trip_id, trip_headsign, trips.block_id, departure_time from rail_data.trips left join rail_data.stop_times on trips.trip_id = stop_times.trip_id where stop_id = '105' order by departure_time asc;

This returns results like this:

+---------------+---------+-----------------------+----------+----------------+
| stop_sequence | trip_id | trip_headsign         | block_id | departure_time |
+---------------+---------+-----------------------+----------+----------------+
|            18 |    1342 | NEW YORK PENN STATION |     6600 | 05:43:00       | 
|             1 |    1402 | SUMMIT                |     6305 | 06:07:00       | 
|            16 |    1328 | NEW YORK PENN STATION |     6604 | 06:34:00       | 
|             1 |    1391 | SUMMIT                |     6307 | 06:41:00       | 
|            19 |    1360 | NEW YORK PENN STATION |     6908 | 06:47:00       | 
+---------------+---------+-----------------------+----------+----------------+

In this case, I only want the trains headed to SUMMIT to show up. But remember I can't simply say where stop_sequence > 1 because I want to include trains that might be the second, third, etc. stop -- just not the final stop.

Thanks in advance for the help!

---

you can query the stop_times and check if its shape_dist_traveled==0 and take the corresponding "trip id" and then query the trips table with this id. so, you have to add a where:

where trips.trip_id in (select st.trip_id from stop_times st where st.shape_dist_traveled==0)

P.S: I assumed the shape_dist_travelled will give the distance travelled by the train