title: How to multiple join the same table?
body: <p>I have the tables Connection and Location with the following columns:</p>
  
  <p><strong>Connection:</strong></p>
  
  <pre><code>LocationIDFrom  
  LocationIDTo 
  Duration
  </code></pre>
  
  <p><strong>Location:</strong></p>
  
  <pre><code>LocationID
  LocationName
  </code></pre>
  
  <p>The example records are the following:
  Connection:</p>
  
  <pre><code>1, 2, 3ms
  2, 1, 5ms
  1, 5, 12ms
  3, 2, 2ms
  </code></pre>
  
  <p>Location:</p>
  
  <pre><code>1, New York
  2, Boston
  3 Los Angeles
  </code></pre>
  
  <p>I wanted to display the table with columns LocationFrom, LocationTo, Duration. The example for the records above would be</p>
  
  <pre><code>New York, Boston, 3ms
  Boston, New York, 5ms
  Los Angeles, Boston, 2ms
  </code></pre>
  
  <p>I tried to use the following query</p>
  
  <pre><code>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
  </code></pre>
  
  <p>but the result was incomplete; there were rows missing which locations details were stored in Location table for both LocationIDFrom and LocationIDTo.</p>
  
  <p>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?</p>
  
  <p><a href="https://stackoverflow.com/questions/2798317/performing-inner-join-for-multiple-columns-in-the-same-table">Here</a> I found the solution for the similar problem but don't know why the principle doesn't work for the problem described above.</p>