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:
- Get all children of product A.
- Get all the parents of the children.
- Filter out all non active (
status= 'DELETED') parents.
- Count the remaining parents for each children.
- 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.