SQL how to JOIN based on 2 ID's-CodePudding

I'm quite new to SQL and I'm playing around in a friend's dataset from an old assignment he did. I have a table with routes including arrival and departure airport codes. Using the join below with a table of airports I can see the name of the arrival airport name in one query but I also want to see the name of the departure airport.

select route.FlightNum, route.DepCode, route.ArrCode, airport.AirportName
from route
left join airport on route.ArrCode=airport.AirportCode

I don't know if a join is what I should be doing in the first place but ideally I would see: FlightNum, Airport Name (for departure), DepCode, Airport Name (for arrival), ArrCode.

Result:

CodePudding user response：

select r.FlightNum, r.DepCode, r.ArrCode, arrport.AirportName,depport.AirportName
from route r
left join airport arrport on r.ArrCode=arrport.AirportCode
left join airport depport on r.DepCode=depport.AirportCode