In SQL bring the master only if no child meets a condition

October 22, 2022

I have this selection to get all elements from the master if there’s one child that fit this condition.

select * from Incidents i
inner join IncidentDetails d on d.IncidentId = i.Id
where d.PosIsException is   null

but, let’s imagine than

MasterRecord
Id = 1
Details
Id=1, PosIsException = "x"
id=2, PosIsException = null

Master record
id=2
details 
id=3, PosIsException = null

master record
id=3
details 
id=4, PosIsException ="x"

In this scenario, the only record that my select need to bring is the master with the id=2, because in the first decision one of the children doesn’t meet the requirement

so, how can I modify the query to bring only the data of the master only if none of their children meet the requirement?

>Solution :

You can check with EXISTS if rows with other values exists

select * from Incidents i
inner join IncidentDetails d on d.IncidentId = i.Id
where d.PosIsException is   null
AND NOT EXISTS ( SELECT 1 FROM IncidentDetails d1 WHERE d.IncidentId = d1.IncidentId AND d1.PosIsException IS NOT  NULL)