title: Left outer join SQL Server
body: <p>I'm confused as to why this is not working, I've used this type of syntax several times but this one as got me pulling my hair out.</p>
  
  <p>I have two tables;</p>
  
  <p><strong>tableA</strong></p>
  
  <pre><code>regId name    regStatus
  1     George  1
  2     Jenny   1
  3     Penny   1
  4     James   1
  5     Preston 1
  6     Jamie   0
  </code></pre>
  
  <p><strong>TableB</strong></p>
  
  <pre><code>activeRegId passiveRegID Status
  1            2           1
  1            3           1
  1            4           0
  6            1           1
  </code></pre>
  
  <p>What I'm trying to do is return all rows from <code>tableA</code> excluding those where <code>(tableA.regstatus = 0)</code> and <code>(tableB.status = 1 for a user regid = 1)</code>. </p>
  
  <p>I want to avoid having to use <code>NOT IN (select ...)</code>.</p>
  
  <p>My query so far:</p>
  
  <pre><code>    select top 10 
          tA.regId, tA.name
      from 
          tableA tA 
      left OUTER JOIN 
          tableB tB ON tB.activeRegId = tA.regid AND tB.passiveRegID &lt;&gt; 1 
                       AND tB.status &lt;&gt; 1 AND tB.passiveRegID IS NULL
      where 
          tA.regStatus = 1
          and tA.regid &lt;&gt; 1
  </code></pre>
  
  <p>What I'm expecting back should be as follows however, I'm getting all the users in <code>tableA</code> except for Jamie. </p>
  
  <pre><code>regId name
  4     James
  5     Preston
  </code></pre>