CODEWITHSUNDEEP
sqlsql-server
Mihai Alexandru-Ionut
Mihai Alexandru-Ionut

Reputation: 48357

How to apply condition for all children in SQL?

I have two tables: Case and Artifact. One Case can have zero or many Artifacts.

Case has CaseId as the Primary column.

Artifact has ArtifactId, CaseId, IsEnabled as columns.

How can I write one condition for all the children for one Case? . What I want to do is to show Cases whose all Artifacts are of a certain status (enabled).

Upvotes: 0

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use not exists:

select c.*
from cases c
where not exists (select 1
                  from artifact a
                  where a.caseid = c.caseid and a.isEnabled = 0
                 );

Upvotes: 3

Related Questions