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:
>
>
> > 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- Hide quoted text -
> - Show quoted text -