title: Screening for data not present through multiple records for each case
body: <p>So I am relatively new to SQL. I have run into something though that has me completely stumped.</p>
  
  <p>I have one table [Tab1], roughly like this:</p>
  
  <pre><code>IDNo  | FileType 
  ------------------------  
     1  | type1
     1  | type2
     1  | type3
     1  | type4
     2  | type2
     2  | type3
     2  | type4
     3  | type1
     3  | type3
     3  | type4
     4  | type2
     4  | type3
  </code></pre>
  
  <p>And then I have another table [Tab2] like this:</p>
  
  <pre><code>  Pno | Status
  ------------------------  
     1  | AC
     2  | CA
     3  | AC
     4  | AC
  </code></pre>
  
  <p><strong>I am needing to screen Tab2, to only display AC status records, while checking Tab1 to see what files are missing for each Pno.</strong> This is the closest I've gotten.</p>
  
  <pre><code>SELECT Pno from Tab2 where Pno NOT IN (
  
  SELECT DISTINCT t1.Pno FROM (SELECT IDNo, Filetype FROM Tab1 where Filetype LIKE 'Type1') As t1 ) AND Status = 'AC'
  </code></pre>
  
  <p>But, this doesn't make sure there aren't repeat records in Tab1 under IDNo. I've gone at this another couple ways, including this method (<a href="https://stackoverflow.com/questions/33784786/how-to-check-if-value-exists-in-each-group-after-group-by">Link</a>) but it didn't work as it was returning false positives. This was my second stab, kind of taking what I got from research and modifying what I had.</p>
  
  <pre><code>SELECT Pno, Status 
  from Tab2 
  where Pno NOT IN (
  SELECT DISTINCT t1.IDNo 
  FROM (
      SELECT IDNo, FileType 
      FROM Tab1 
      where FileType LIKE 'Type 1') 
  As t1 ) 
  AND Status &lt;&gt; 'CA'
  Order by Tab2.Pno
  </code></pre>
  
  <p>This time, I still got false positives.</p>
  
  <p>Can anyone help on this?</p>
  
  <p>So the output should be: </p>
  
  <pre><code>  Pno 
  ------- 
     4
  </code></pre>
  
  <p>As only PNo 1 and 3 are of Ac status and possess a Type1 File</p>