Select children of product, with no other active parents

Question

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.

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'
AND    NOT EXISTS (
   SELECT
   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:

• Select rows which are not present in other table

Related answer using a LEFT JOIN:

• DELETE rows which are not referenced in other table