How to find a record in a many-to-many relationship where all its related records meet a condition?-CodePudding

I have a table called 'Jobs'. Each job has a status and can depend on 0 or more other jobs, so there's also a join-table to track this many-to-many relationship. I'm trying to find the jobs whose dependencies have succeeded.

Jobs table:

Id | Name | Status
------------------
1  | A    | WaitingForDependency
2  | B    | WaitingToRun
3  | C    | PartiallySucceeded
4  | D    | Succeeded

Join table:

JobId | DependsOn
-----------------
1     | 3
1     | 4

Status can be:

WaitingForDependency
WaitingToRun
Running
PartiallySucceeded
Succeeded
Failed

In the example tables above, I want to locate job A because its dependencies C and D have a status of either Succeeded or PartiallySucceeded.

CodePudding user response：

See if this works for you, one way could be to use exists to check the count of qualifying statuses of linked jobs matches the count of linked jobs:

select * 
from jobs j
where exists (
  select *
  from jointable t
  left join jobs js on js.Id = t.DependsOn 
    and js.[status] in ('PartiallySucceeded','Succeeded')
  where t.jobId = j.Id 
  group by t.jobId
  having Count(*) = Count(js.[status])
);