Stuck in "In clause"

Question

I have following tables:

 create table passenger ( passenger_ID varchar ( 10 ) , passenger_name varchar ( 30 ) , passenger_city varchar ( 30 ) , primary key ( passenger_ID ) ) ;
    create table flight ( flight_number varchar ( 10 ) , departure_airport varchar ( 10), arrival_airport varchar ( 10 ) , primary key ( flight_number ) );

    create table seat ( 
    flight_number varchar ( 10 ),
    seat_number varchar ( 10 ),
    primary key(flight_number, seat_number),
    foreign key(flight_number) references flight);

    create table reservation(
        passenger_ID varchar ( 10 ) 
        flight_number varchar ( 10 ) , seat_number varchar ( 10 ) , 
    day date ,
        fare numeric(8,2),
    primary key (flight_number, seat_number,day),
    foreign key (flight_number, seat_number) references seat ) ,
    foreign key (passenger_ID) references passenger ;

I am stuck in this question: Find the ID of every passenger that reserved a flight (or flights) departing from ’ALB’ and has never reserved a flight arriving at ’ALB’. For this query, use either an in clause or a not in clause.

I have written this much query:

select a.passenger_id from 
reservation a 
where a.flight_number in (select b.flight_number from flight b where 
                                        b.departure_airport = 'ALB' 
                                    and b.arrival_airport <> 'ALB');

But this query is not correct. Subquery returns all the flight numbers that matches the filter condition. But the main select query returns all the passenger id for the flight_number returned by the subquery, even if that passenger's arrival_airport is ALB.

My reservation table:

 passenger_id   flight_number   seat_number day fare
2   A2  201 10/1/17 1083
1   A3  301 10/1/17 1173.25
1   A4  402 10/1/17 846.81
2   A5  501 10/1/17 752.72
1   A5  502 10/1/17 485
4   A5  506 10/1/17 970
2   A6  601 10/1/17 388
1   A7  703 10/1/17 921.5
3   A7  704 10/1/17 921.5
2   A8  804 10/1/17 970
4   A8  805 10/1/17 970

My flight table:

flight_number   departure_airport   arrival_airport
A1  Ktm Pkr
A2  Ktm NY
A3  Ktm Ind
A4  Ktm Chn
A5  ALB KTM
A6  ALB PKR
A7  KTM ALB
A8  PKR ALB

Answer 1

Concentrate on the passengers instead of the flights, using IN() and NOT IN()

SELECT DISTINCT
      r.passenger_id
FROM reservation r
WHERE r.passenger_id IN (
      SELECT
            r.passenger_id
      FROM RESERVATION r
      INNER JOIN FLIGHT f ON f.flight_number  = r.flight_number 
      WHERE f.DEPARTURE_AIRPORT = 'ALB'
    )
AND r.passenger_id NOT IN (
      SELECT
            r.passenger_id
      FROM RESERVATION r
      INNER JOIN FLIGHT f ON f.flight_number  = r.flight_number 
      WHERE f.ARRIVAL_AIRPORT = 'ALB'
    )
;

But the analysis by Bob Jarvis does show that no rows in your sample data meets the assignment criteria.

Answer 2

Just to demonstrate the correctness of @GordonLinoff's answer in a slightly different manner, let's look at it slightly differently. :-) (See this SQLFiddle). (Note that I've populated the PASSENGER table. We'll use that later. :-). First, let's grab all the passengers who have reserved at least one flight departing from ALB:

-- Find passengers who have reserved at least one flight departing from ALB

SELECT DISTINCT r.PASSENGER_ID
  FROM RESERVATION r
  INNER JOIN FLIGHT f
    ON f.FLIGHT_NUMBER = r.FLIGHT_NUMBER
  WHERE f.DEPARTURE_AIRPORT = 'ALB';

This returns PASSENGER_ID's 1, 2, and 4.

Next, let's find all the passengers who have reserved at least one flight arriving at ALB:

-- Find passengers who have reserved at least one flight arriving at ALB

SELECT DISTINCT r.PASSENGER_ID
  FROM RESERVATION r
  INNER JOIN FLIGHT f
    ON f.FLIGHT_NUMBER = r.FLIGHT_NUMBER
  WHERE f.ARRIVAL_AIRPORT = 'ALB';

This returns PASSENGER_ID's 1, 2, 3, and 4.

A trivial examination of the above results demonstrates clearly that there are no passengers who have reserved a flight departing ALB who have not reserved a flight arriving in ALB. But because we'd like to just have the query show us what we want instead of having to think (after all, that's what computers are supposed to do :-) we'll put the above queries together:

-- Now put them together

SELECT p.PASSENGER_ID
  FROM PASSENGER p
  INNER JOIN (SELECT DISTINCT r.PASSENGER_ID
                FROM RESERVATION r
                INNER JOIN FLIGHT f
                  ON f.FLIGHT_NUMBER = r.FLIGHT_NUMBER
                WHERE f.DEPARTURE_AIRPORT = 'ALB') d
    ON d.PASSENGER_ID = p.PASSENGER_ID
  LEFT OUTER JOIN (SELECT DISTINCT r.PASSENGER_ID
                     FROM RESERVATION r
                     INNER JOIN FLIGHT f
                       ON f.FLIGHT_NUMBER = r.FLIGHT_NUMBER
                     WHERE f.ARRIVAL_AIRPORT = 'ALB') a
    ON a.PASSENGER_ID = p.PASSENGER_ID
  WHERE a.PASSENGER_ID IS NULL;

This query returns no results, as expected.

But...we really want to make sure that our query really does work. Soooo...let's add a couple of rows to a couple tables. First, we'll add a new passenger:

insert into passenger (passenger_ID, passenger_name, passenger_city)
  values ('5', 'Bugs Bunny', 'ABC');

And we'll add a flight for Mr. Bunny, one where he departs ALB:

INSERT INTO RESERVATION (PASSENGER_ID, FLIGHT_NUMBER, SEAT_NUMBER, DAY, FARE)
  VALUES ('5', 'A6', '123', TO_DATE('2017-10-01','YYYY-MM-DD'), 400);

So now we have passenger 5, Mr. B. Bunny, departing ALB - but he's never flown into ALB. (I think that's because he shoulda made a left turn at Albuquerque :-). So our query should return passenger 5, and if you go look at this SQLFiddle you'll see that's exactly what happens.

Ehhh - best of luck, doc.

Answer 3

Think: Two conditions. So from your line of thinking:

select r.passenger_id
from reservation r
where r.flight_number in (select f.flight_number
                          from flight f
                          where f.departure_airport = 'ALB' 
                         ) and
      r.flight_number not in (select f.flight_number
                              from flight f
                              where f.arrival_airport = 'ALB' 
                             );

However, that doesn't do it. A any flight arriving at ALB left from some other airport. So, this just chooses all passengers who reserved a flight from ALB. This logic is at the flight level, not the passenger level.

Hmmmm. You actually want information on all flights a customer has "at once". That suggests aggregation:

select r.passenger_id
from reservation r join
     flight f
     on r.flight_number = f.flight_number
group by r.passenger_id
having sum(case when f.departure_airport = 'ALB' then 1 else 0 end) > 0 and -- reserved a flight from ALB
       sum(case when f.arrival_airport = 'ALB'  then 1 else 0 end) = 0;     -- never reserved a flight to ALB