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!

• Laurenz Albe: There are two child rows for parent 1 where status <> 'at_home', so why shouldn't it be returned? Perhaps you could clarify your condition.
• jewnbug97: @LaurenzAlbe yes, it is just a custom business requirement. There can be multiple status's for child rows present against a parent but as soon as a status exists with at_home, I don't want any parent rows to be returned

Answer 1

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

• jewnbug97: Nice! Does this also return the parent when no child records are present for it?
• Laurenz Albe: Sure, because then there is also no child that satisfies the NOT EXISTS condition.