How to multiple join the same table?

I hav开发者_如何学Goe the tables Connection and Location with the following columns:

Connection:

LocationIDFrom  
LocationIDTo 
Duration

Location:

LocationID
LocationName

The example records are the following: Connection:

1, 2, 3ms
2, 1, 5ms
1, 5, 12ms
3, 2, 2ms

Location:

1, New York
2, Boston
3 Los Angeles

I wanted to display the table with columns LocationFrom, LocationTo, Duration. The example for the records above would be

New York, Boston, 3ms
Boston, New York, 5ms
Los Angeles, Boston, 2ms

I tried to use the following query

SELECT l1.LocationName AS LocationFrom, l2.LocationName AS LocationTo, c.Duration
FROM Connection c
  INNER JOIN Location l1 ON l1.LocationID= c.LocationIDFrom
  INNER JOIN Location l2 ON l2.LocationID= c.LocationIDTo

but the result was incomplete; there were rows missing which locations details were stored in Location table for both LocationIDFrom and LocationIDTo.

Does anyone know how to modify the query above or write the correct one which returns the table with location names which have to be retrieved from the same table for LocationFrom and LocationTo instead of IDs?

Here I found the solution for the similar problem but don't know why the principle doesn't work for the problem described above.

It's because you can't join on ID 5 because it doesn't exist. If you left outer join you'll pick up the connection and just won't have a name for the locations that don't exist.

SELECT isNull(l1.LocationName,'') AS LocationFrom, isNull(l2.LocationName,'') AS LocationTo, c.Duration 
FROM Connection c 
left outer JOIN Location l1 ON l1.LocationID= c.LocationIDFrom
left outer JOIN Location l2 ON l2.LocationID= c.LocationIDTo