SQL previous, next row

Question

2 table, one with flights, one with destination. I want all flights with departure and arrival who are in the table 2 (work for me). But i want so that arrival follow departure. See example Thanks for all

CREATE TABLE flights
        (`Name` varchar(10), `Departure` varchar(50), `Arrival` varchar(10), `Pass` int, `Cargo` int, `Dist` int)
    ;

    INSERT INTO flights
        (`Name`, `Departure`, `Arrival`, `Pass`, `Cargo`, `Dist`)
    VALUES
        ('444 737vvv', 'LFLL gee', 'LPMAdsf', 200, 2000, 12),
        ('gg737vvv', 'LPMA-egege', 'LFLLdsf', 3000, 0, 13),
        ('747vvv', 'LFLLèèegege', 'LPMAdsf', 0, 5000, 15),
        ('747vvv', 'OTHHèèegege', 'LPMAdsf', 0, 5000, 15),
        ('747vvv', 'OMDBèèegege', 'LPMAdsf', 0, 5000, 15),
        ('a320vvv', 'EGKK-egege', 'LFPOdd', 0, 6000, 14)
    ;



    CREATE TABLE Regular
        (`Dep` varchar(21), `Arri` varchar(21),`Type` varchar(21))
    ;

    INSERT INTO Regular
        (`Dep`, `Arri`, `type`)
    VALUES
        ('LFLL', 'LFPG', 'cargo'),
        ('LFPG', 'LFLL', 'cargo'),
        ('LFLL', 'LPMA', 'com'),
        ('LPMA', 'LFLL', 'cargo'),
        ('LFPO', 'EGKK', 'cargo'),
        ('EGKK', 'LFPO', 'com')
    ;

With this request i have all flight where Departure et Arrival are in Regular table. Good! But i want so only flight who arrival follow departure.

Exemple today with this :

 $query = "Select
    flights.UserName,
    flights.FlightDate,
    flights.FlightResult,
    flights.AircraftName,
    flights.DepartureIcaoName,
    flights.ArrivalIcaoName
    from
    flights
    inner join regular
    on regular.Departure = SUBSTRING(flights.DepartureIcaoName,1,4) and regular.Arrival = SUBSTRING(flights.ArrivalIcaoName,1,4);";

I Have :

('444 737vvv', 'LFLL gee', 'LPMAdsf', 200, 2000, 12),
    ('gg737vvv', 'LPMA-egege', 'LFLLdsf', 3000, 0, 13),
    ('747vvv', 'LFLLèèegege', 'LPMAdsf', 0, 5000, 15),
    ('a320vvv', 'EGKK-egege', 'LFPOdd', 0, 6000, 14)

I want :

('444 737vvv', 'LFLL gee', 'LPMAdsf', 200, 2000, 12),
    ('gg737vvv', 'LPMA-egege', 'LFLLdsf', 3000, 0, 13),
    ('747vvv', 'LFLLèèegege', 'LPMAdsf', 0, 5000, 15),

I think to (but dont work) :

$query = "Select 
flights.*, regular.* 
from flights, regular 
where regular.Departure = SUBSTRING(flights.DepartureIcaoName,1,4) 
and regular.Arrival = SUBSTRING(flights.ArrivalIcaoName,1,4) 
and SUBSTRING(flights.DepartureIcaoName,1,4) = (SUBSTRING(flights.ArrivalIcaoName,1,4) - 1)
";

Answer 1

A different way to make your query is:

SELECT
    flights.name,
    flights.Departure
FROM
    flights
    INNER JOIN Table2
        ON CONCAT(Table2.Dep,Table2.Arri) = CONCAT(SUBSTRING(flights.Departure,1,4),SUBSTRING(flights.Arrival,1,4));

but it produces the same result.

The problem is that in your example the flight a320vvv has a Departure-Arrival couple string EGKK LFPO in flights and in Table2 you have both EGKK LFPO and LFPO EGKK. So the query is doing right.

Indeed, if you make SELECT * you see that the result is:

where the selected flight is the com one, with EGKK LFPO departure arrival, and not the cargo one, that has departure arrival LFPO EGKK.

Regards