Help With Query and/or Design

I am currently trying to model a database and have great difficulty in
determining the correct structure.
General Subject - Aircraft Flights.
Table - Flight ( FlightID = PK, FlightNumber, ClientNumber etc.... ))
Table - FlightLegs (FlightLegID = PK , FlightLegID = FK Date,
AirportDepartID = FK, AirportArrivalID = FK, etc...)
Table - Airports ( AiportID = PK , AirportName, City , Country
etc...) ,
PK = Primary Key , FK = Foreign Key .
The ForeignKeys require a double liaison between FlightLegs and
Airports. ( Is this a bad idea ?).
1 : Each flight may have one or more flightlegs.
2 : Each FlightLeg has a Depart Airport and an Arrival Airport.
For a given FlightNumber I wish to be able to display a list of all
flightlegs and the appropriate AirportNames.
I am having difficulty trying to create queries where I can get both
"AirportNames" for a given flightleg. I can easily retrieve either the
Departure or Arrival name but cannot seem to get both in one query.
I have simplified my examples for clarity.
No problem here
SELECT AirportName from Airports WHERE Airports.AirportID =
FlightLeg.AirportDepartID FROM Airports
Problem here
SELECT AirportName as Departure,AirportName as Arrival from Airports
WHERE Airports.AirportID = FlightLeg.AirportDepartID AND
Airports.AirportID = FlightLeg.AirportArrivalID FROM Airports
I realise that the above can never work due to the AND condition.
I know that the following works but I don't like this solution.
SELECT
( SELECT AirportName from Airports WHERE Airports.AirportID =
FlightLeg.AirportDepartID FROM Airports) Departure,
(SELECT AirportName from Airports WHERE Airports.AirportID =
FlightLeg.AirportArrivalID FROM Airports) Arrival
FROM Airports
what would be a more elegant solution, is my model incorrect.
On Apr 30, 2:11 pm, r.whiteh...@.gyrolan.com wrote:
> I am currently trying to model a database and have great difficulty in
> determining the correct structure.
> General Subject - Aircraft Flights.
> Table - Flight ( FlightID = PK, FlightNumber, ClientNumber etc.... ))
> Table - FlightLegs (FlightLegID = PK , FlightLegID = FK Date,
> AirportDepartID = FK, AirportArrivalID = FK, etc...)
> Table - Airports ( AiportID = PK , AirportName, City , Country
> etc...) ,
> PK = Primary Key , FK = Foreign Key .
> The ForeignKeys require a double liaison between FlightLegs and
> Airports. ( Is this a bad idea ?).
> ----
> 1 : Each flight may have one or more flightlegs.
> 2 : Each FlightLeg has a Depart Airport and an Arrival Airport.
> ----
> For a given FlightNumber I wish to be able to display a list of all
> flightlegs and the appropriate AirportNames.
> I am having difficulty trying to create queries where I can get both
> "AirportNames" for a given flightleg. I can easily retrieve either the
> Departure or Arrival name but cannot seem to get both in one query.
> I have simplified my examples for clarity.
> No problem here
> SELECT AirportName from Airports WHERE Airports.AirportID =
> FlightLeg.AirportDepartID FROM Airports
> Problem here
> SELECT AirportName as Departure,AirportName as Arrival from Airports
> WHERE Airports.AirportID = FlightLeg.AirportDepartID AND
> Airports.AirportID = FlightLeg.AirportArrivalID FROM Airports
> I realise that the above can never work due to the AND condition.
> I know that the following works but I don't like this solution.
> SELECT
> ( SELECT AirportName from Airports WHERE Airports.AirportID =
> FlightLeg.AirportDepartID FROM Airports) Departure,
> (SELECT AirportName from Airports WHERE Airports.AirportID =
> FlightLeg.AirportArrivalID FROM Airports) Arrival
> FROM Airports
> what would be a more elegant solution, is my model incorrect.
SELECT FlightLegID,
(SELECT AirportName FROM Airports WHERE Airports.AirportID
=FlightLeg.AirportDepartID ) as AirportDepart,
(SELECT AirportName FROM Airports WHERE Airports.AirportID
=FlightLeg.AirportArrivalID ) as AirportArrival
FROM FlightLegs
|||Here is another alternative.
SELECT
fl.FlightLegID,
dep.AirportName DepartureAirport,
arr.AirportName ArrivalAirport
FROM
FlightLegs fl
JOIN Airports dep ON dep.AirportID = fl.AirportDepartID
JOIN Airports arr ON arr.AirportID = fl.AirportArrivalID
On Apr 30, 7:33 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Apr 30, 2:11 pm, r.whiteh...@.gyrolan.com wrote:
>
>
>
>
>
>
>
>
>
>
> SELECT FlightLegID,
> (SELECT AirportName FROM Airports WHERE Airports.AirportID
> =FlightLeg.AirportDepartID ) as AirportDepart,
> (SELECT AirportName FROM Airports WHERE Airports.AirportID
> =FlightLeg.AirportArrivalID ) as AirportArrival
> FROM FlightLegs- Hide quoted text -
> - Show quoted text -