Select parent records where ALL children records have a specific status

user_9618070 0
Viewed 1099

There is table A with records-parents. In table B are records-children for records from table A, the relationship between them by parent ID. Children can have the status "open" or "closed". A parent can have multiple children in table B.

How to display records from table A that have all children in table B closed? Otherwise do not display

sql-server-2014 sql-server sql
5 Answers

You can check that no not-closed record exists using NOT EXISTS query:

SELECT *
FROM TableA
WHERE NOT EXISTS (
    SELECT 1
    FROM TableB
    WHERE TableB.ParentID = TableA.ID
    AND TableB.Status <> 'closed'
)
user_870150

I believe this should do it:

select a.id
from tablea a
join tableb b on 
  a.id = b.parentid
group by a.id
having count(*) = sum(case when b.status = 'closed' then 1 else 0 end)

The key in this solution is to compare if count of records found for particular parent equals count of records for the same parent with status closed.

user_20955340

This will return a parent if there's no child record (which might be wrong):

SELECT *
FROM tabA AS A
WHERE NOT EXISTS
 (
   SELECT * 
   FROM tabB AS B
   WHERE b.Parent = a.id
     AND b.status <> 'closed'
 )

This returns a parent if there're child records and all are 'closed':

SELECT *
FROM tabA
WHERE id IN
 (
   SELECT Parent
   FROM tabB
   GROUP BY Parent
   HAVING Max(CASE WHEN status = 'closed' THEN 0 ELSE 1 END) = 0
 )
user_25279050

Try the following query-

Select A.* from A join B on A.ID=B.parentID
where B.status='closed'

SQL Server 2014

user_88805170

You can use join to achieve this

SELECT * FROM Table_A INNER JOIN Table_B on Table_A.Id = Table_B.ParentId WHERE 
Table_B.Status='closed';
user_79152070
Related