title: Left outer join with specific record in one table
body: <p>There are two tables first <code>Audit_exception</code> and second <code>finding_escalation</code>. I need to join both the table using left outer join. Each record/exception in audit_exception table having corresponding record in finding_escalation table but there might be no corresponding records or having single record or multiple records. Basically 1:M relationship.</p>
  <p>Tables</p>
  <p>Audit_Exception</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Objid</th>
  <th>Exception_id</th>
  <th>Description</th>
  <th>Comment</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1234</td>
  <td>1</td>
  <td>Abcd</td>
  <td>Abcd</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Finding_escalation</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Objid</th>
  <th>Finding_objid</th>
  <th>Esc_level</th>
  <th>Esc_type</th>
  <th>Latest_esc</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>10001</td>
  <td>1234</td>
  <td>1</td>
  <td>Escalation</td>
  <td>1</td>
  </tr>
  <tr>
  <td>10002</td>
  <td>1234</td>
  <td>1</td>
  <td>Warning</td>
  <td>0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Requirement : I need to join both the table but only with latest escalation record if multiple record is present in <code>finding_escalation</code> table. If no record is present in <code>finding_escalation</code> table then according to left outer join the only data of Audit_exception should be in output  result.</p>
  <p>Problem : If I am using normal left outer join then I am getting multiple records in output query if finding_escalation table having multiple records for same finding.  I can’t use ANSI standard LEFT OUTER JOIN ON keyword because query is written oracle based (+) operator syntax. How to add extra condition on joining so that join will happen with finding_escalation latest record only.</p>
  <p>Query 1 :</p>
  <pre class="lang-sql prettyprint-override"><code>select * from audit_exception ae, finding_escalation esc 
  where ae.objid = esc.finding_objid (+) 
  and ae.objid=1234;
  </code></pre>
  <p>This query is working fine when <code>finding_escaltion</code> table having no record or single record the output is single record. But resulting with multiple records when <code>finding_escalation</code> table also having multiple records.</p>
  <p>Query 2:</p>
  <pre class="lang-sql prettyprint-override"><code>select * from audit_exception ae, finding_escalation esc
  where ae.objid = esc.finding_objid (+)
  and esc.LATEST_RECORD = 1
  and ae.objid=1234
  </code></pre>
  <p>This query is working fine when <code>finding_escaltion</code> table having single or multiple records. But not working if there is no records in <code>finding_escaltion</code> table, the output is blank.</p>
  <p>My expectation : I need a single output irrespective of finding_escalation table has any record or multiple records or no records.</p>
  <p>Please let me know if my query is not clear.</p>