Simple Join Problem

[Suleman007]

Hi,
I need a little help regarding a simple SQL query problem.

I have a table (Airport_Detail), which has information abt the airports:

Airport_ID:
Name:
Code:
etc..

Now I have another table Flight_Detail, which has the following fields:

Flight_ID:
From_Airport_ID: (FK from Airport_Detail table mapped with Airport_ID)
To:Airport_ID: (FK from Airport_Detail table mapped with Airport_ID)
etc..

Now I want to retreive records from the above two tables in the following order:

Flight_ID
Name (against From_Airport_ID)
Name (against To_Airport_ID)
etc..


The above should be the fields in each record.

I hope the description is clear. I'm little out of practice, so need a little help to revise these things -

--
Regards Suleman

 

[PHV]

SELECT A.Flight_ID, F.Name, T.Name
FROM Flight_Detail A
INNER JOIN Airport_Detail F ON A.From_Airport_ID = F.Airport_ID
INNER JOIN Airport_Detail T ON A.To_Airport_ID = T.Airport_ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Suleman007]

Indeed it worked !!!! I changed it a bit, but ur idea really clicked.

SELECT A.Prov_ID, P.Name, A.Flight_ID, F.Name, T.Name
FROM Provider P, Flight_Detail A, Airport_Detail F, Airport_Detail T
WHERE A.Prov_ID = P.Prov_ID
AND A.From_Airport_ID = F.Airport_ID
AND A.To_Airport_ID = T.Airport_ID
LIMIT 0 , 30

Thankyou very much for a simple and prompt reply. Really appreciated !!!

 

[r937]

Suleman007, you needn't have changed it, you should really try to write your queries with JOIN syntax, as in PHV's example

mysql has no problem with JOIN syntax, and while the "table list" syntax, with the join conditions in the WHERE clause, will work for inner joins, it will work only for inner joins



r937.com | rudy.ca