Return only rows under certain constraints in join 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!
SELECT * FROM parent
WHERE NOT EXISTS (SELECT 1 FROM child
                  WHERE child.parent_id = parent.id
                  AND child.status = 'at_home');