CODEWITHSUNDEEP
t-sql
DNg
DNg

Reputation: 452

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

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:

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.

Upvotes: 0

Views: 85

Answers (1)

Stu
Stu

Reputation: 32609

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])
);

Upvotes: 1

Related Questions