title: Database Multiple Inner Join
body: <p>I have question regarding the performance factor for the following question.</p>
  
  <p>Consider a table : Users,</p>
  
  <pre><code> -----userId------- userName-----
          1              ABC
          2              XYZ
  </code></pre>
  
  <p>Another table : Transaction,</p>
  
  <pre><code>------createdBy----ReceivedBy----TransactionId
            1            2              50
            2            1              51
  </code></pre>
  
  <p>My required result is,</p>
  
  <pre><code>--------createdBy----ReceivedBy----TransactionId----userName_Creator-----userName_Receiver
              1            2               50            ABC                     XYZ
              2            1               51            XYZ                     ABC
  </code></pre>
  
  <p>I came out with two solutions,</p>
  
  <ol>
  <li>Using a varied inner join,</li>
  </ol>
  
  <blockquote>
    <p>SELECT * FROM
        Transaction T
        INNER JOIN
        (
          SELECT createdBy,userName FROM Transaction
          Inner Join
          Users</p>
  
  <pre><code>)
  ON createdBy = userId
  INNER JOIN
  (
    SELECT ReceivedBy,userName FROM Transaction
    Inner Join
    Users
  )   
  ON ReceivedBy= userId
  WHERE TransactionId = 51
  </code></pre>
  </blockquote>
  
  <p>2.To maintain an hashmap for all the distinct userId and username and package the output by a look up in the data layer.</p>
  
  <p>Which of these solution is optimal ? Also better technique is welcome.</p>
  
  <p>--Siva</p>