Return only rows under certain constraints in join

Question

I am using PostgreSQL 11.6 and I have two tables: a parent and a child table. The child table is associated to the parent in that it has a parent_id column and a parent can have many child rows associated. The child also has a status column that I'd like to filter on.

Here are my requirements. I need to return all rows from the parent table where either:

• there is NO associated child row present
• there are child rows present but none of those rows have a value in their status column of at_home

E.x.

parent table
+----+
| id |
+----+
|  1 |
|  2 |
+----+

child table
+------------------------+
| id  parent_id   status |
+------------------------+
| 1   1       at_school  |
| 2   1       at_shop    |
| 3   1       at_home    |
+------------------------+

My query should return only the parent with an id of 2 since that parent doesn't have an associated child record. The parent with an id of 1 should only been returned if none if it's present child records had an at_home status. With my query below, it is currently still being returned (which is incorrect).

I'm trying a query like this right now but it's not working because I'm filtering on child where the status is != to at_home, but it still returns a parent record because rows 1 and 2 of the child records above match that condition:

SELECT parent.* FROM parent LEFT OUTER JOIN child ON child.parent_id = parent.id WHERE (child.id is NULL OR child.status != 'at_home')

Any help would be appreciated. Thanks!

Answer 1

SELECT * FROM parent
WHERE NOT EXISTS (SELECT 1 FROM child
                  WHERE child.parent_id = parent.id
                  AND child.status = 'at_home');