0

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!

2
  • 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. Apr 15, 2020 at 15:47
  • @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
    – jewnbug97
    Apr 15, 2020 at 15:57

1 Answer 1

2
SELECT * FROM parent
WHERE NOT EXISTS (SELECT 1 FROM child
                  WHERE child.parent_id = parent.id
                  AND child.status = 'at_home');
2
  • Nice! Does this also return the parent when no child records are present for it?
    – jewnbug97
    Apr 15, 2020 at 16:34
  • Sure, because then there is also no child that satisfies the NOT EXISTS condition. Apr 15, 2020 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.