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

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?

CodePudding user response：

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)