MySQL: Select only if field values are ascending

I have two tables: Route and Stop (one to many). And also I have input data: two location IDs which is related to ‘stop’ table. Every route has it’s own ORDERED list of stops. I need to select Route based on the stops order (OLNY where stop.order is ascending) having start and finish locations.

Data example:

| ROUTE            |
--------------------
| id | name        |
--------------------
| 1 | first        |
--------------------
| 2 | second       |
--------------------

---------------------------
| STOP                    |
---------------------------
|id | order |loc_id|route_id|
----------------------------
| 1 | 1     |  1   |  1   |
---------------------------
| 2 | 2     |  2   |  1   |
---------------------------
| 3 | 3     |  3   |  1   |
---------------------------
| 4 | 3     |  1   |  2   |
---------------------------
| 5 | 2     |  2   |  2   |
---------------------------
| 6 | 1     |  3   |  2   |
---------------------------

USE CASE: for example, i have 3 locations: Los Angeles (id=1), Chicago (id=2) and New York (id=3). Also there are 2 bus routes: LA to NY and NY to LA. It means i have 3 bus-stops based on given locations for every route but they have opposite order. So i need to create MySQL query to select appropriate route having start and finish locations. For example if i have Chicago as starting point and LA as finish, the query have to return to me NY to LA route. If i have Chicago and NY locations – it should get LA to NY route.

That’s the query i was able to create but of course it doesn’t work

SELECT `route`.* 
FROM `route`
WHERE (SELECT stop.order FROM `stop` WHERE stop.locationId = 1) < (
        SELECT stop.order FROM `stop` WHERE stop.locationId = 2)

(where 1 and 2 are location IDs input data)

I need to have this result if i have loc_id 1 and 2

--------------------
| id | name        |
--------------------
| 1 | first        |
--------------------

Therefore, if loc_id = 2 and 1 i need to have next result:

--------------------
| id | name        |
--------------------
| 2 | second       |
--------------------

Answer

Try this:

SELECT R.id, R.name
FROM Stop S
INNER JOIN Route R ON R.id = S.route_id
WHERE 
S.loc_id = 1 AND 
EXISTS (SELECT Id FROM Stop F WHERE S.route_id = F.route_id AND F.order > S.order AND F.loc_id = 2);

Advertisement

Answer