CODEWITHSUNDEEP
mysqlsql
Mohammad
Mohammad

Reputation: 3547

select where all rows having same foreign key, meet the condition

I have two tables, operation and operationTask. let's say that operation only has

and operationTask has

The relation between these two tables is one-to-many.

I want to select all operations where all of their tasks "operationtask" status is equal to 1.

What I have tried:

SELECT * 
FROM `operation` 
WHERE operation.id = All(
    SELECT task.operation_id 
    FROM operationtask task 
    WHERE task.status=1 
    GROUP BY task.operation_id)

for example:

operation:

ID
---
1
2
3

operationtask:

ID   operation_id   status
---  ------------   ------
1         1           1
2         1           0
3         2           1
4         2           1
5         3           0
6         3           0

the result should be:

operation:

ID
---
2

Upvotes: 0

Views: 1744

Answers (1)

Tobb
Tobb

Reputation: 12225

select *
from operations o
where not exists (
      select 1 
      from operationtask t 
      where t.operation_id = o.id and t.status = 0)

Upvotes: 1

Related Questions