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 filters children that have 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

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

Making several assumptions, this query might work your you:

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
AND    c_prod.product_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.product_status = 'ACTIVE'
   HAVING count(*) > 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 filters children that have 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