title: Can INNER JOIN replace a column's value with another value from the same table - for all rows
body: <p>I need to write a query to replace one of the column's values with a value from another row in the same table.  I tried to use a simple inner join, but I'm not seeing the results I need.   </p>
  
  <p>Here's an example of the table BANK_LOANS:</p>
  
  <pre><code>MONTH_ID   LOAN_NUMBER    BANK_NAME       AMOUNT
  ========   ===========    ==========      ========
  76         00-100         Bank One        100.00
  75         00-100         The Bank One    150.00
  74         00-100         Bank 1          150.00
  76         00-200         Another Bank    300.00 
  75         00-200         Another Bank    500.00
  </code></pre>
  
  <p>The data is loaded from outside sources which doesn't verify the bank name, so the spelling for a bank name for a given loan number can possibly vary.   The customer wants whatever BANK_NAME is in the database for the max MONTH_ID to be used for all the results for that LOAN_NUMBER, and they want the results sorted by BANK_NAME.   So for instance, loan 00-100 needs a BANK_NAME of "Bank One" for all rows.</p>
  
  <p>I tried using an inner join to do this, but wasn't getting the correct results</p>
  
  <pre><code>SELECT name.BANK_NAME, 
         bl.LOAN_NUMBER,
         bl.AMOUNT
  FROM BANK_LOANS    
  INNER JOIN BANK_LOANS home
      ON name.LOAN_NUMBER = bl.LOAN_NUMBER
      AND name.MONTH_ID = 67  --the max id provided to the query
  ORDER BY name.BANK_NAME, bl.LOAN_NUMBER, bl.MONTH_ID DESC
  </code></pre>
  
  <p>I think I have it working with an sub-query, but it's kind of ugly. I was wondering if there was a better way (best practices) of accomplishing this with joins or other oracle functions.</p>
  
  <p>This seems to work, but it just feels wrong:</p>
  
  <pre><code>SELECT name.BANK_NAME, 
         bl.LOAN_NUMBER,
         bl.AMOUNT
  FROM BANK_LOANS, 
       (SELECT bl2.BANK_NAME, bl2.LOAN_NUMBER
        FROM BANK_LOANS
        WHERE bl2.MONTH_ID = 76  --max month id provided to query
       ) name
  WHERE name.LOAN_NUMBER = bl.LOAN_NUMBER
  ORDER BY name.BANK_NAME, bl.LOAN_NUMBER, bl.MONTH_ID DESC
  </code></pre>