Thread: Query Problem

If I've 2 tables like this:

Country table : with 2 fields (country and id)

Country             id
England            E
France               F
Japan                J

FlightTo table : with 2 fields(Flight ID and Destination)

FlightID            Destination
B1                     E,J
B2                     E,F
B3                     J,F


How do I make a query, so that I can get a result something like this :

FlightID          CountryVisited
B1                    England,Japan
B2                    England,France
B3                    Japan, France


Any help very much appreciated. Thanks

Abdul Wahab Dahalan wrote:
> If I've 2 tables like this:
> 
> Country table : with 2 fields (country and id)
> 
> Country             id
> England            E
> France               F
> Japan                J
> 
> FlightTo table : with 2 fields(Flight ID and Destination)
> 
> FlightID            Destination
> B1                     E,J
> B2                     E,F
> B3                     J,F

Simplest solution is to correct this table - "Destination" should be two 
fields (e.g. dest_from,dest_to). Then you could do something like:

SELECT  flight_id,  c1.country as from_country,  c2.country as to_country
FROM flight_to, country c1, country c2
WHERE  dest_from = c1.id  AND dest_to = c2.id
;
--  Richard Huxton  Archonet Ltd