SQL to select rows where all associations have not null values-CodePudding

I have two tables: workflows and tasks. Tasks belong to a workflow.

I want to select all workflows where all the tasks associated with them are complete.

For example:

Workflow 1

Task A - completed_at = 2020-01-01
Task B - completed_at = null

Workflow 2

Task C - completed_at = 2020-01-01
Task D - completed_at = 2020-01-01

Workflow 3

Task E - completed_at = 2020-01-01
Task F - completed_at = 2020-01-01

I want to run a query that only selects workflow 2 and 3 since their tasks are complete and have no incomplete tasks.

CodePudding user response：

You may try "not in subquery" as follows:

select * 
from workflows
where workflow_id not in (select workflow_id 
                            from tasks 
                           where completed_at is null)