Select Unique Parent IDs based on values of child IDs

Transact-SQL
,
1

From this table what is the most efficient way of selecting a column of distinct ParentMeetingID values where all of its ChildMeetingID values do not have a Finalised value of 2 please?
So a select script executed on the example table should return a column with values of 2343, 2348, and 2388.
None of the columns will ever be NULL.
Thank you.

ParentMeetingID ChildMeetingID Finalised
2315 2316 2
2317 2318 2
2317 2366 1
2317 2367 2
2317 2368 2
2325 2365 2
2335 2341 2
2343 2344 0
2343 2345 0
2343 2355 0
2346 2347 2
2348 3395 0
2349 2350 2
2351 2352 2
2353 2354 2
2357 2358 2
2357 2359 2
2357 2360 2
2357 2361 2
2362 2551 2
2388 2389 1
2388 2390 1
2388 2391 1
2

Use GROUP BY and HAVING:

SELECT ParentMeetingID
  FROM dbo.YourTable
 GROUP BY
       ParentMeetingID
HAVING SUM(CASE WHEN Finalised <> 2 THEN 1 ELSE 0 END) > 0;

This will also return 2317 in the results since one of the rows for that parent has a child where Finalised = 1.

3

Ok thanks for that, but 2317 should also be discounted because it has one or more values of 2 for Finalised. Sorry if that wasn't made clear in my description.

4

This should work:

SELECT ParentMeetingID
  FROM dbo.YourTable
 GROUP BY
       ParentMeetingID
HAVING MAX(Finalised)<2;
1 Like
5 
SELECT ParentMeetingID
FROM dbo.YourTableName
GROUP BY ParentMeetingID
HAVING SUM(CASE WHEN Finalised = 2 THEN 1 ELSE 0 END) = 0;
1 Like