I have 2 tables with below info
Table# 1 – TaskDependecy
TaskID | DependentTaskID |
---|---|
1 | 2 |
1 | 3 |
3 | 6 |
3 | 7 |
5 | 8 |
Table # 2 – TaskStatus
TaskID | Task Status |
---|---|
1 | In Progress |
2 | Completed |
3 | In Progress |
5 | In Progress |
6 | Completed |
7 | Completed |
8 | Completed |
I want to retrieve only taskIDs # 3 and 5 so that I can update status of these taskIDs as Completed since all its dependent tasks are in completed status.
How to achieve this in SQL?
Advertisement
Answer
You can use conditional aggregation
for this, which should work in either MSSQL or MySQL
x
select td.taskId
from TaskDependency td
join TaskStatus ts on ts.taskId = td.DependentTaskId
group by td.TaskId
having Sum(case when ts.TaskStatus='completed' then 1 end) = Count(*)