Passengers

SELECT s.seat_no, p.passenger_name, p.email
FROM seats s
LEFT OUTER JOIN (
SELECT t.passenger_name, b.seat_no,
t.contact_data->'email' AS email
FROM (
ticket_flights tf
JOIN tickets t ON tf.ticket_no = t.ticket_no
)
JOIN boarding_passes b
ON tf.ticket_no = b.ticket_no
AND tf.flight_id = b.flight_id
WHERE tf.flight_id = 27584
) AS p
ON s.seat_no = p.seat_no
WHERE s.aircraft_code = 'SU9'
ORDER BY
left( s.seat_no, length( s.seat_no ) - 1 )::integer,
right( s.seat_no, 1 );