Two field with same foreign key

[ingiB]

I have two table: flight_info and cities

flight_info has fields:
flightNo Source Destination
125 1 4
432 5 2
777 6 3

cities has:
cityID Name
1 London
2 Dublin
3 Paris
4 Chicago
5 New York
6 Boston

Source and Destination are both foreign keys for cityID in cities.

I want to show
FlightNo Source Destination
125 London Chicago

I can get
flightNo Source(name)
125 London

but I'm having trouble with joining destination to the same thing as source.

Any advice????

 

[PHV]

Use TWO instances of the cities table with aliasing:
SELECT F.flightNo, S.Name Source, D.Name Destination
FROM flight_info F
INNER JOIN cities S ON F.Source = S.cityID
INNER JOIN cities D ON F.Destination = D.cityID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886