3

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.

3

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:

Related answer using a LEFT JOIN:

  • i added more details. i hope it helps. – Marko Taht Apr 5 '18 at 13:58
  • @MarkoTaht: Consider the updated answer. – Erwin Brandstetter Apr 9 '18 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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