Given these two tables in Postgres 9.6:

CREATE TABLE product (
  id bigint PRIMARY KEY,
  status text

CREATE TABLE products_m_products (
  parent_product_id bigint REFERENCES product,
  child_product_id  bigint REFERENCES product

How to select all active children of a given product that have no other active parent?

As I understand it needs to be something like this:

  1. Get all children of product A.
  2. Get all the parents of the children.
  3. Filter out all non active (status = 'DELETED') parents.
  4. Count the remaining parents for each children.
  5. Keep children whose parent count is 1 or less.

But how do I put this into a query?

I tried this:

select distinct child, parent 
from product as child, 
     product as parent,
     (select child_product_id 
          from products_m_products 
          where parent_product_id = 1337) as children,
     products_m_products as parents
where child.id = children.child_product_id 
    AND parents.child_product_id = children.child_product_id 
    AND child.product_status = 'ACTIVE'
    AND parents.parent_product_id = parent.id 
    AND parent.product_status = 'ACTIVE';

But I'm not sure if this is correct.

We can assume that A is active, but it should be excluded from the final result since A will be deactivated.


1 Answer 1


This query should do it:

SELECT child.child_product_id
FROM   products_m_products child
JOIN   product             c_prod ON c_prod.id = child.child_product_id
WHERE  child.parent_product_id = 1337     -- user input here
AND    c_prod.status = 'ACTIVE'
   FROM   products_m_products parent
   JOIN   product             p_prod ON p_prod.id = parent.parent_product_id
   WHERE  parent.child_product_id = child.child_product_id
   AND    p_prod.status = 'ACTIVE'
   AND    parent.parent_product_id <> 1337  -- same user input

The anti-semi-join with NOT EXISTS only keeps children with no other active parent than the given A. This query does not care whether the given parent A is active itself.

Should be as fast as possible.

There are several basic techniques to check there is no related row in another table:

Related answer using a LEFT JOIN:

  • i added more details. i hope it helps.
    – Marko Taht
    Commented Apr 5, 2018 at 13:58
  • @MarkoTaht: Consider the updated answer. Commented Apr 9, 2018 at 13:03

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.