--
-- Show the passengers who have flown out of one airport but have never flown
-- out of another airport.
--

DROP FUNCTION IF EXISTS never_flown(airport1 text, airport2 text);

CREATE FUNCTION never_flown(airport1 text, airport2 text)
RETURNS TABLE (passenger_id int, first_name text, last_name text) AS $$

  SELECT passenger_id, first_name, last_name
  FROM passenger
    JOIN booking_leg USING (booking_id)
    JOIN flight USING (flight_id)
  WHERE departure_airport = $1
  EXCEPT
  SELECT passenger_id, first_name, last_name
  FROM passenger
    JOIN booking_leg USING (booking_id)
    JOIN flight USING (flight_id)
  WHERE departure_airport = $2

$$ LANGUAGE SQL STABLE STRICT;

ALTER FUNCTION never_flown(airport1 text, airport2 text) OWNER TO absent;
