Check if a group belonging to another table has set of certain ID's

Question

Here's the scenario, I'm trying to get a set of IDs in one table that contain certain statuses in another.

Table S is this main table. Table S has a column called S.StatusID which should be at the number 4 (Completed Status) when the other table OS, has its status equal one of multiple numbers. These OS.StatusID's are 4, 5, 7 or 8.

Table S can contain one or more of OS, so if there were 3 OS's in Table S, for S to be considered at Completed Status (4), all the OS Statuses should be any of the OS statuses of 4, 6, 7 or 8.

S ID	OS Status
1600	6
1600	8
1600	4

So, in that table, S would be considered at Completed Status (4).

Now, in our website while the code works in setting everything correctly now. Some tables in the DB do not match this standard, and we want to find all the S ID's that are not at S.StatusID 4 and force an DB call to change these values to the correct status.

This is my current attempt, but it doesn't consider them as a whole group to work:

-- Additional note, anything before S.StatusID 3 an OS will not exist and anything > 3 is already complete or expired making it irrelevant, hence the Where clause.

Select Distinct S.SID

From STable S
    left Join OSTable OS On S.SID = OS.SID
Where S.StatusID = 3
 
Group By S.SubmissionID
Having Count(CASE OS.StatusID WHEN 4 THEN 1 END) > 0 
and Count(CASE OS.StatusID WHEN 6 THEN 1 END) > 0 
and Count(CASE OS.StatusID WHEN 7 THEN 1 END) > 0 
and Count(CASE OS.StatusID WHEN 8 THEN 1 END) > 0   

I have found some similar threads (TSQL Find If All Records In A Group Have The Same Value, SQL check if group containes certain values of given column (ORACLE)) but I can't find one that answers my question with multiple things being checked to match.

Just to be clear, I want to find any S IDs that contain OS's at OS.Status 4, 6, 7 or 8, but are not at S.Status 4.

How could I write this SQL?

Thanks!

Answer 1

The logic statement "all x are y" is equivalent to "no x is not y". The logic you need then is roughly NOT EXISTS(OS WHERE OS.Status NOT IN (...)).

Something like:

SELECT *
FROM STable S
WHERE S.Status = 3
AND NOT EXISTS (
    SELECT *
    FROM OSTable OS
    WHERE OS.SID = S.SID
    AND OS.Status NOT IN (4, 6, 7, 8)
)

Note that if by chance there are no matching OS records at all, the condition is still true.