Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    20

    Unanswered: Using WHERE, IN, or AND correctly

    I need to display all flights that both originate (fl_orig) and arrive (fl_dest) at an airport without a hub airline.

    select fl_flight_no, fl_orig, fl_dest, air_code
    from flight, airport
    where air_code = fl_orig and air_code = fl_dest
    and air_hub_airline is null;

    There are 3 originations and 3 destinations without a hub airline.
    Some results could have, for example, LAX and SFO as the fl_orig and fl_dest since neither have a hub airline.
    So I'm not able to use fl_orig = fl_dest.

    I've also tried without success:

    select fl_flight_no, fl_orig, fl_dest, air_code, count(*)
    from flight, airport
    where air_code in (fl_orig, fl_dest)
    and air_hub_airline is null
    group by fl_flight_no, fl_orig, fl_dest, air_code;

    Any other suggestions?
    Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    There are 2 airports involved, so join to the airports table twice:
    Code:
    select fl_flight_no, fl_orig, fl_dest
    from   flight
    join   airport ap_orig on ap_orig.air_code = fl_orig 
    join   airport ap_dest on ap_dest.air_code = fl_dest
    where  ap_orig.air_hub_airline is null
    and    ap_dest.air_hub_airline is null;

  3. #3
    Join Date
    Apr 2009
    Posts
    20
    works great now. thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •