SQL help finding court cases where all children have been closed.

[acicchillo]

Hi all -
I know the title is confusing but I need help finding the court cases where all children associated with that case have a programClosureDate.
I can run this query:

CaseInfo Table
CaseID,
CaseNumber,
CaseName

CaseChild Table
CaseID, FK to CaseInfo
ChildPartyID, FK to PartyID in Party table
ProgramClosureDate

Party Table
ID,
PartyID,
Firstname,
LastName




SELECT ci.CaseNumber, ci.CaseName, p.firstname+' '+p.lastname AS child, cc.programClosureDate
FROM CaseInfo ci JOIN
CaseChild cc ON ci.CaseID = cc.CaseID JOIN
Party p ON cc.ChildPartyID = p.PartyID

WHERE cc.ProgramClosureDate IS NOT NULL
ORDER BY ci.CaseName

But this does not give me the cases where all the children have programCLosureDate IS NOT NULL.
Any help is greatly appreciated.
Andy

[gk53]

Why not just simple

Select distinct CaseID from CaseChild where ProgramClosureDate is null

[June7]

Maybe:

WHERE NOT cc.ProgramClosureDate IS NULL

[gk53]

June7
if you need not it will be
WHERE cc.ProgramClosureDate IS NOT NULL