By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH flight AS (
SELECT 'LH100' AS flightnr, 'FRA' AS start, 'ATL' AS dest UNION ALL
SELECT 'LH102', 'JFK', 'LAX' UNION ALL
SELECT 'LH103', 'ATL', 'PEK'
),
departure AS (
SELECT 'LH100' AS flightnr, '2018-01-10' AS date, 'D-ABBL' AS sign UNION ALL
SELECT 'LH102', '2018-03-30', 'D-ABBL' UNION ALL
SELECT 'LH103', '2018-01-10', 'D-ABBL'
)
SELECT
d1.date AS date_start,
f1.flightnr AS flightnr_start,
f1.start AS first_start,
f1.dest AS change_over_dest,
d2.date AS date_end,
f2.flightnr AS flightnr_end,
f2.start AS last_start,
f2.dest AS change_over_end
FROM flight f1
INNER JOIN departure d1
ON f1.flightnr = d1.flightnr
INNER JOIN flight f2
ON f1.dest = f2.start
INNER JOIN departure d2
ON f2.flightnr = d2.flightnr AND d1.date = d2.date
WHERE
f1.start = 'FRA' AND f2.dest = 'PEK';
| date_start | flightnr_start | first_start | change_over_dest | date_end | flightnr_end | last_start | change_over_end |
|---|---|---|---|---|---|---|---|
| 2018-01-10 | LH100 | FRA | ATL | 2018-01-10 | LH103 | ATL | PEK |