SQL join help needed 2

[dephjam]

hi there,
I have an airport table like this:

************
airports
------------
air_id
air_code
air_name
***********

and a flight table:

************
flight
------------
fl_id
fl_code
fl_depart_airport_id
fl_arrive_airport_id
***********

i need the select statement to get names for departure and arrivel airports. something like this:

***********************
fl_code
departure_airport_name
arrival_airport_name
***********************
any help much appreciated

 

[jrbarnett]

SELECT fl_code,
  (select depart.airport_name
   FROM airports depart
   WHERE depart.fl_id = flights.fl_id) As DepartureAirport,

  (select arrive.airport_name
   FROM airports arrive
   WHERE arrive.fl_id = flights.fl_id) As ArrivalAirport,
FROM flights

John

 

[JarlH]

If the solution above, with sub-queries, turns out to be too slow, try this instead:
[tt]
SELECT fl_code,
depart.air_name,
arrive.air_name
FROM flight,
airports AS depart,
airports AS arrive
WHERE depart.air_id = fl_depart_airport_id
AND arrive.air_id = fl_arrive_airport_id
[/tt]