title: INNER JOIN for two columns of the same foreign key
body: <p>Suppose that we have two tables:</p>
  
  <p>TABLE TA</p>
  
  <pre><code>AID BID1 BID2
  --  ---- ----
  01  01   02
  02  01   03
  03  02   01
  </code></pre>
  
  <p>TABLE TB</p>
  
  <pre><code>BID Name
  --- ----
  01  FOO
  02  BOO
  03  LOO
  </code></pre>
  
  <p>If I want to return the following:</p>
  
  <pre><code>AID Name1
  --  -----
  01  FOO
  02  FOO
  03  BOO
  </code></pre>
  
  <p>I write the following:</p>
  
  <pre><code>SELECT TA.AID, TB.Name as Name1 
  FROM TB 
  INNER JOIN TA on TB.BID = TA.BID1
  </code></pre>
  
  <p>However, I cannot figure out how to return the TB.Name that correspond to both the BID1 and BID2. More specifically I want to return the following:</p>
  
  <pre><code>AID Name1 Name2
  --  ----- -----
  01  FOO   BOO
  02  FOO   LOO
  03  BOO   FOO
  </code></pre>