title: SQL LEFT outer join with only some rows from the right?
body: <p>I have two tables <code>TABLE_A</code> and <code>TABLE_B</code> having the joined column as the employee number <code>EMPNO</code>. </p>
  
  <p>I want to do a normal left outer join. However, <code>TABLE_B</code> has certain records that are soft-deleted (<code>status='D'</code>), I want these to be included. Just to clarify, <code>TABLE_B</code> could have active records (status= null/a/anything) as well as deleted records, in this case i don't want that employee in my result. If however there are only deleted records of the employee in <code>TABLE_B</code> i want the employee to be included in the result.I hope i'm making my requirement clear. (I could do a lengthy qrslt kind of thingy and get what I want, but I figure there has to be a more optimized way of doing this using the join syntax). Would appreciate any suggestions(even without the join). His newbness is trying the following query without the desired result:</p>
  
  <pre><code>SELECT TABLE_A.EMPNO
  FROM   TABLE_A
  LEFT OUTER JOIN TABLE_B ON TABLE_A.EMPNO = TABLE_B.EMPNO AND TABLE_B.STATUS&lt;&gt;'D' 
  </code></pre>
  
  <p>Much appreciate any help.</p>