SQL Multiple columns same join column

Question

I have a table with 3 columns in my table Travel (and of course some more):

AirportFrom, AirportTo, AirportFound.

The columns above display ID's from airports. In the table Airports are 2 columns AirportID and AirportName. Instead of displaying the ID's from the airports I want to display the AirportNames.

But when I use:

SELECT id
     , AirportFrom
     , Airports.Airportname
     , AirportTo
     , Airports.Airportname
     , AirportFound
     , Airports.Airportname 
  FROM Travel 
  LEFT 
  JOIN Airports 
    ON AirportTo = Airports.AirportID
-- LEFT JOIN Airports ON AirportFrom = Airports.AirportID
-- LEFT JOIN Airports ON AirportFound = Airports.AirportID

It only displays the airport name of the first join in every column. I want to show the airport name for each of the 3 joins

Che-Chia Chang · Accepted Answer · 2017-01-13 15:35:43Z

3

Provide multiple aliases for joined table each time you left join, and join them as you have multiple tables:

SELECT 
    Travel.id, 
    airport_to.Airportname as to_name, 
    airport_from.Airportname as from_name, 
    airport_found.Airportname as found_name, 
FROM Travel 
LEFT JOIN Airports airport_to ON Travel.AirportTo = airport_to.AirportID
LEFT JOIN Airports airport_from ON Travel.AirportFrom = airport_from.AirportID
LEFT JOIN Airports airport_found ON Travel.AirportFound = airport_found.AirportID

EDIT: Replace reserved words in table aliases. Thanks for the reminder!

edited Jan 13, 2017 at 15:35

answered Jan 13, 2017 at 15:05

Che-Chia Chang

2912 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

radarbob Over a year ago

up vote. better readability and understandability vis-a-vis other answer

Strawberry Over a year ago

watch out for reserved words

Gordon Linoff · Accepted Answer · 2017-01-13 15:04:25Z

1

Your query needs table aliases (for multiple joins to the same table). Then, be sure to use qualified columns names for all columns in a query. This helps you write correct queries and it helps you and other people understand what is going on. So:

SELECT t.id, t.AirportFrom, apt.Airportname,
       t.AirportTo, apf.Airportname,
       t.AirportFound, apfo.Airportname 
FROM Travel t LEFT JOIN
     Airports apt
     ON t.AirportTo = apt.AirportID LEFT JOIN
     Airports apf
     ON t.AirportFrom = apf.AirportID LEFT JOIN
     Airports apfo
     ON t.AirportFound = apfo.AirportID;

answered Jan 13, 2017 at 15:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL Multiple columns same join column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related