SQL- Retrieve records based on column’s specific value

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?

Answer

You can use conditional aggregation for this, which should work in either MSSQL or MySQL

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(*)

Example Fiddle