title: sql left outer join not working for multiple columns
body: <p>we have a query which should return 5 values and n/a if any of them are not in database. Here is the query</p>
  
  <pre><code>SELECT NVL(IR16.invoice_refnum_value, 'N/A') AS GL_CODE1,
         NVL(IR15.invoice_refnum_value, 'N/A') AS GL_AMOUNT1, 
         NVL(IR17.invoice_refnum_value, 'N/A') AS GL_RECEIVING_BU,
         NVL(IR18.invoice_refnum_value,'N/A') AS GL_SHIPPING_BU,
         NVL(IR19.invoice_refnum_value, 'N/A') AS GL_SALES_ORDER_NUMBER
      FROM   invoice i2 
      LEFT outer JOIN invoice_refnum ir16 
        ON i2.invoice_gid = ir16.invoice_gid 
      LEFT outer JOIN invoice_refnum ir15 
        ON i2.invoice_gid = ir15.invoice_gid
      LEFT outer JOIN invoice_refnum ir17 
        ON i2.invoice_gid = ir17.invoice_gid
      LEFT outer JOIN invoice_refnum ir18 
        ON i2.invoice_gid = ir18.invoice_gid
      LEFT outer JOIN invoice_refnum ir19 
        ON i2.invoice_gid = ir19.invoice_gid
      where ir15.invoice_refnum_qual_gid like 'GL AMOUNT%' 
        AND ir16.invoice_refnum_qual_gid like 'GL CODE%' 
        AND ir17.invoice_refnum_qual_gid like 'GL RECEIVING BU%'
        AND ir18.invoice_refnum_qual_gid like 'GL SHIPPING BU%'
        AND ir19.invoice_refnum_qual_gid like 'GL SALES ORDER NUMBER%'
        AND i2.invoice_gid = 'TEST' 
        and regexp_substr(ir16.invoice_refnum_qual_gid,'\d+$') = regexp_substr( ir15.invoice_refnum_qual_gid,'\d+$')
        and regexp_substr(ir16.invoice_refnum_qual_gid,'\d+$') = regexp_substr( ir17.invoice_refnum_qual_gid,'\d+$')
        and regexp_substr(ir16.invoice_refnum_qual_gid,'\d+$') = regexp_substr( ir18.invoice_refnum_qual_gid,'\d+$')
        and regexp_substr(ir16.invoice_refnum_qual_gid,'\d+$') = regexp_substr( ir19.invoice_refnum_qual_gid,'\d+$')
  </code></pre>
  
  <p>But what is happening is if anytime there is a value missing for even one refnum qual( i.e. it is not in database) it will just omit the entire row. it should not do this, it should just replace missing value with N/A and display rest of 4 values.</p>
  
  <p>Sample data expected to be working like:</p>
  
  <ul>
  <li>test1 test1 test1 test1 test1 
  <ul>
  <li>test2 test2 N/A   test2 test2 </li>
  <li>test3 test3 test3 N/A   test3 </li>
  </ul></li>
  <li>test4 test4 test4 test4 test4</li>
  </ul>
  
  <p>how is current results coming:</p>
  
  <ul>
  <li>test1 test1 test1 test1 test1</li>
  <li>test4 test4 test4 test4 test4</li>
  </ul>
  
  <p>any pointers will be greatly appreciated. i dont know if i am approaching this thing the right way!</p>