Mysql how to join these tables -

i have following tables , sample data.

schedule table fightno   day_of_week  orgin    dest  depart_time   arrive_time     totalfare aq2131    wed          blr      kta   04:30         11:00           6000  flight_stops (if have stops) flightno  day_of_week  airport_code   arrival_time  departure_time  stopmilesfare aq2131    wed          bom            02:17         05:40           3000  aq2131    wed          coh            03:17         06:40           3000  aq2131    wed          goy            04:17         08:40           3000 

the flight route blr -> bom -> coh -> goy -> kta

how can join these tables if user enters of these source , destination , retrieve flightno. example if user enters blr source , coh destination or coh source , goy destination, can find flight number.

first, lets step moment. flights intended 1 direction d, or d 2 different flight numbers. have have proper correlation of direction proper flight. grins, imaginary international

flight #123 country -> b -> c -> d. flight #987 country d -> c -> b -> a. 

both trips have country 'c' , 'b'... if our trip start @ 'c' , end @ 'b', need flight #987 since need on plane @ 'c' , stop @ 'b' -- in direction. flight #123 fail on @ 'a' off @ 'b' , got 'c' stop picked up. use 'ccc' , 'bbb' comply 3 char airport codes queries.

first query (which part of union all) flights start @ given location... , either destination or 1 of legs of trip destination of desired 'bbb' location. using left/join on same flight , destination airport code, if not null, there leg destination.

select        s1.flightno           schedule s1          left join flight_stops fs1             on s1.flightno = fs1.flightno             , fs1.airport_code = 'bbb'               s1.origin = 'ccc'       , (    s1.dest = 'bbb'            or fs1.flightno not null ) 

this next query (the union one) flight started somewhere else , has 1 leg of flight starting flight of trip ('ccc'). join schedule table see if destination of flight 'bbb' in question. if not that, looks @ flight stops table again, 'bbb' has arrival time after of 'ccc' starting flight time.

select        fs1.flightno           flight_stops fs1          left join schedule s2              on fs1.flightno = s2.flightno             , s2.dest = 'bbb'           left join flight_stops fs2              on fs1.flightno = fs2.flightno             , fs1.arrival_time < fs2.arrival_time             , fs2.airport_code = 'bbb'           fs1.airport_code = 'ccc' 

since getting flight number, union two. since flight can't both start , leg @ same time, never pull same flight. if wanted actual rest of flight info, wrap too.

so, final query of flights

(first select above) union (second select above) 

with flight header info...

select       s.*          ( (first select above)          union          (second select above) ) qualflights       join schedule s          on qualflights.flightno = s.flightno 

now, said, easier if flight_stops table had inclusively origin , destination locations. then, entire query simplified , having index on flight_stops... (airport_code, flightno, arrival_time )

  select            fs1.flightno                   flight_stops fs1              left join flight_stops fs2                  on fs1.flightno = fs2.flightno                 , fs1.arrival_time < fs2.arrival_time                 , fs2.airport_code = 'bbb'                   fs1.airport_code = 'ccc' 

yes... 1 query having both origination , destination in "legs" of flight table need.