ITPEC FE Morning April 2022 Question 27

Self-join with aliases — join the same table twice using different foreign keys.

The query needs flight code, origin city name, and destination city name from Flight and City.

Since both OriginCityID and DestinationCityID reference the same City table, a self-join with two aliases is required:

• •City c1 → joined on Flight.OriginCityID = c1.CityID
• •City c2 → joined on Flight.DestinationCityID = c2.CityID

Correct SQL:
- SELECT FlightCode, c1.CityName, c2.CityName
- FROM Flight, City c1, City c2
- WHERE Flight.OriginCityID = c1.CityID AND Flight.DestinationCityID = c2.CityID

Why not others:
- (a) single City with no alias — both conditions force origin = destination (same city)
- (b) selects OriginCityID, DestinationCityID (IDs, not names)
- (d) aliases Flight twice instead of City — joins two different flights, not two cities

Key rule: When a table must be referenced by two different foreign keys in the same query, use self-join with aliases on that table.

AI-generated — may contain errors