For some reason I cannot get my head wrapped around this. I have two tables and I need to return just one field from one of them when all of the dependent rows in the joined table meet certain criteria. A simplified version of tables:
SELECT c.RegionAcronym FROM cmpnt c
LEFT JOIN Office o
ON o.OfficeCode = c.OfficeCode AND o.LimitSwitch = 'Y'
AND (o.CloseDate = NULL or o.CloseDate > CURRENT_DATE
GROUP BY c.RegionAcronym
HAVING COUNT(o.OfficeCode) = COUNT(c.OfficeCode)
If I understand correctly, then
NOT EXISTS seems like a good way to express this:
SELECT c.RegionAcronym FROM cmpnt c WHERE NOT EXISTS (SELECT 1 FROM Office o WHERE o.OfficeCode = c.OfficeCode AND NOT (o.LimitSwitch = 'Y' AND (o.CloseDate IS NULL OR o.CLoseDate > CURRENT_DATE) ) )
In terms of performance, this does not require aggregation.