Screening for data not present through multiple records for each case

Question

So I am relatively new to SQL. I have run into something though that has me completely stumped.

I have one table [Tab1], roughly like this:

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

And then I have another table [Tab2] like this:

  Pno | Status
------------------------  
   1  | AC
   2  | CA
   3  | AC
   4  | AC

I am needing to screen Tab2, to only display AC status records, while checking Tab1 to see what files are missing for each Pno. This is the closest I've gotten.

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'

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 (Link) 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.

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 <> 'CA'
Order by Tab2.Pno

This time, I still got false positives.

Can anyone help on this?

So the output should be:

  Pno 
------- 
   4

As only PNo 1 and 3 are of Ac status and possess a Type1 File

Perhaps add a table showing us what the correct output would look like — Degan, May 10 '17 at 20:16
RBDMS is an abbreviation. What is the name of the database product you use? — , May 10 '17 at 20:59
I'm sorry, my head stopped working a moment there. MS SQL Server. — EF87, May 10 '17 at 21:08

Degan · Answer 1 · 2017-05-11T14:23:35.933

0

IN the OP's comment below, the desired output is Status = 'AC' with no FileType = Type1. While this does not match the desired output listed in the question, this is a query that would achieve that result:

Select Pno
From Tab2
Where Status = 'AC'
And Pno not in (Select IDNo
                From Tab1
                Where FileType= 'type1')

Looking at the given Tab1 and Tab2, the output should be: 4

edited May 11 '17 at 14:23

answered May 10 '17 at 20:57

Degan

989
2
16
30

Well, because what I need isn't where the Status = 'AC', it's where the Status = 'AC' and there are no Type1 forms. I'll try to make that clearer in my question. – EF87 May 10 '17 at 20:59
@EF87, In your output, you have 1 and 3. In Tab1, you have the records, 1, Type1 and 3,Type1. From your comment above, I would have thought 1 and 3 should be excluded, and 4 should be included. Please clarify and update your question. – Degan May 11 '17 at 14:19
Apologies, you're correct. This is why I should be more careful about posting things on little sleep. Question has been updated. Also, I and a guy I was working with found the answer. I will be posting it shortly. – EF87 May 11 '17 at 18:43

Screening for data not present through multiple records for each case

1 Answers1