1

I have 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.

Community
  • 1
  • 1
Niko Gamulin
  • 66,025
  • 95
  • 221
  • 286
  • 1
    I don't understand how there could be rows missing. Could you please elaborate on that? I assume you have referential integrity, i.e. every location ID in Connections really exists in Location... – Daniel Hilgarth May 12 '11 at 14:00
  • 1
    Your query looks correct to me. Are there cases where either location ID could be NULL? If so, you'd want to use `LEFT JOIN` instead of `INNER JOIN`. – Joe Stefanelli May 12 '11 at 14:01
  • My mistake! The query returns the correct result in case location details for all IDs are stored in Location table. I overlooked an incorrect entry. – Niko Gamulin May 12 '11 at 14:34

1 Answers1

7

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
Avitus
  • 15,640
  • 6
  • 43
  • 53