December 1, 2007 at 10:50 pm
I have a airport table with airportID as primary key.
I have an AirRoute table which has startingAirportID, stopoverID and destinationID all referenced to airportID.
All airportIDs are assigned particular cities thru cityID.
I am unable to write a query for displaying the startAirportID, its city, stopoverID, city related to its ID and destID, city all at the same time.
Can someone please suggest me a solution for this?
December 2, 2007 at 5:00 am
You need to use the same airport table three times in a query -> you need table aliases...
Example
select
airRoute.routeID,
airportStart.airportName,
airportStopOver.airportName,
airportDest.airportName
from airRoute
inner join airport airportStart
on airRoute.startingAirportID = airportStart.airportID
left join airport airportStopOver
on airRoute.stopOverID = airportStopOver.airportID
left join airport airportDest
on airRoute.destinationID = airportDest.airportID
Note the left join - I assume not all routes have a stopover. The same airport table is effectively appearing three times in the query independently of the other uses of the airport table. It is as though you had duplicated the data into two identical tables just for the purpsoe of your query.
Read up on "self joins" to see the concept further.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply