joins of a sort :)

[Gnub]

I have two tables. 

1. with a list of airports, and their codes.

2. with a list of flights, with flight data and airport codes.

 

I have a query which is working fine in gathering all the flight information from table 2.  Now, when i post the data in a table, i want to show the name of the airport, however, in table 2, it doesn't contain the name of the airport, only the airport code.

 

I want to get the airport name from table 1, while still maintaining the error free query that i have mentioned above.

 

I've tried the Select `table1`.*, `table2`.* from `table1`, `table2` where Working query AND `table1`.airportcode = `table2`.aircode order by what-ever.

 

and try to post the data from table 2, but i get no results where as before i got some.

 

How would i go about doing this?

 

if you need code snippets, just ask.

[skali]

Post the proper query..

[Gnub]

What i forgot to mention was that there are 3 fields in table 2 that hold airport codes. 

 

Now, below, i've found a way of having one of the fields linked to the 1 airportcode on table1, and i've managed to get the airport name from that link. 

 

I've tried to replicate that for the 2 other fields, but i just get a "no, look at your SQL again"

 

sqlQuery = "Select `INTERNET_FLIGHTS`.*, `Airport`.Aircode, `Airport`.Name, DATE_FORMAT
(`OBDEPDATE`, '%a %D %b') AS revised_date FROM `INTERNET_FLIGHTS`, `Airport` WHERE " & 
Departure & Destination & Nights & " AND `OBDEPDATE` BETWEEN '" & (request.form("DeptDateFrom"))
& "' AND '" &(request.form("DeptDateTo"))& "' AND `INTERNET_FLIGHTS`.FROMAPT = `Airport`.Aircode 
ORDER by COMPUTEDADPRICE ASC Limit 50;"

[skali]

Are you writing this query in PHP ???

[Barand]

Basically you have something like this

[pre]

FLIGHT                AIRPORT

----------            ----------------

departure  ----+ 

dep_time      +----  code

arrival    ----+      name

arr_time

[/pre]

 

To resolve this in a query and fetch both airport names, join twice to the airport table

SELECT d.name, f.dep_time, a.name, f.arr_time
FROM flight f
    INNER JOIN airport d ON f.departure = d.code
    INNER JOIN airport a ON f.arrival = a.code