1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Joining tables with multiple conditions

Discussion in 'SQL PL/SQL' started by hiswapna, Jan 26, 2011.

  1. hiswapna

    hiswapna Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Hi

    I am trying to join two tables and came up with a big huge query with many conditions. Not able to think of an easy alternative to accoplish the same.

    Trips

    msg_id, trip_nbr flight_number trip_origin trip_dest
    123 1 992 LPB MIA
    123 2 239 MIA DFW

    Schedule

    Flight_nbr Leg_nbr Leg_origin Leg_dest flight_origin flight_destination sch_dept
    992 1 MIA LPB MIA TUL 23-JUL-10
    992 2 LPB VVI MIA TUL 24-JUL-10
    992 3 VVI MIA MIA TUL 24-JUL-10
    992 4 MIA TUL MIA TUL 25-JUL-10
    239 1 MIA DFW MIA DFW 24-JUl-10
    Expected output

    123 1 992 LPB VVI 24-JUL
    123 2 992 VVI MIA 24-JUL
    123 3 239 MIA DFW 24-JUL


    select
    trip_flight_number,
    rownum leg_order_number,
    leg_origin,
    leg_destination,
    from (

    select t.*, s.*
    from trips t, flight_schedule s
    where s.flight_number = t.trip_flight_number
    and ( s.leg_origin = ( select distinct s.flight_origin from flight_schedule s
    where s.flight_number = t.trip_flight_number
    and s.flight_date = t.flight_date
    and (s.leg_origin = t.trip_origin or s.flight_origin = t.trip_origin)
    )
    or s.flight_origin = (select distinct s.flight_origin from flight_schedule s
    where s.flight_number = t.trip_flight_number
    and s.flight_date = t.flight_date
    and (s.leg_origin = t.trip_origin or s.flight_origin = t.trip_origin)
    )
    )
    and ( s.leg_destination = ( select distinct s.flight_destination from flight_schedule s
    where s.flight_number = t.trip_flight_number
    and s.flight_date = t.flight_date
    and (s.leg_origin = t.trip_origin or s.flight_origin = t.trip_origin)
    )
    or
    s.flight_destination = ( select distinct s.flight_destination from flight_schedule s
    where s.flight_number = t.trip_flight_number
    and s.flight_date = t.flight_date
    and (s.leg_origin = t.trip_origin or s.flight_origin = t.trip_origin)
    )
    )
    and (s.leg_origin = t.trip_origin or s.leg_destination = t.trip_destination)

    and s.flight_date = t.flight_date
    and t.msg_id = 123
    );


    Is there any other easy alternative possible to acheive this
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Field shown in sql query is different from that mention at the string of post. please provide create table, insert table script along with the expected data. You cant expect teh answer unless you take some effort to explain the query in detail.