how to select column true if any other row is true in this situation?

I'm trying to figure out how to set a column to true when other columns from the query be true.

I have two tables called products and families. A family can have a lot of products and a product can have a main product.

When I execute my select, I should:

• All products that don't have a family
• All products that have a family
• All products that have a family and is a main product (if the product is main product, i don't need to recover others product from this family, only the main product)

My problem is that a have a column called product_launch and it's easy to recover when the product came separately. So I'm trying to set a field of the main product that returns in my query a following rule: If any product of this family has a flag product_launch as true I should set the main product product_launch column to true even if the main product is false.

In this fiddle I have an example that I'm trying to do.

I already tried using any, over/window, and aggregate functions but nothing was able to reach my objective.

My goal at the end of this example is to return for example Playstation 3 with product_launch as true because it's a main product and PlayStation 5 which is in the same family has product_launch true. In the other cases, it's already working because it brings separately.

In this example I have the following rules:

• Pepsi and Cocacola should return because they dont have family.
• Macbook 2015 and macbook 2018 belogs to family (Macbooks) but any of them is a main product so I should receive them in select.
• Family Consoles the only product that could return is playstation 3 because it is main product from Family Consoles. But it need to change the flag product_launch to true because in family (Consoles) there is a product (Playstation 5) that have this flag true.
  If I have some product in family that have flag product_launch as true I should return main product with this flag true even if the main product has flag product_launch as false.

Hope someone can help me to know what to do in Postgresql to reach this goal. Thanks!

I'm not sure I fully understand what you want. I added a "playstation 4" row that's not the main_product, so its product_launch value is not changed. If this isn't what you want the other answers are probably easier.

Query #1

SELECT p.id, p.name, p.family_id, p.enabled
     , CASE
         -- We're on the family's main product, so we can check if any other products in the family are "product_launch"
         WHEN p.id = f.main_product_id THEN bool_or(product_launch) OVER (PARTITION BY family_id)
         ELSE product_launch
       END AS product_launch
     , f.id, f.name, f.main_product_id
from products as p
left outer join families as f
  on p.family_id = f.id
ORDER BY family_id, p.id;

View on DB Fiddle

Edit:

Updating the answer to remove unwanted rows from the final select:

SELECT *
FROM (
  	SELECT p.id, p.name, p.family_id, p.enabled
       	 , CASE
             -- We're on the family's main product, so we can check if any other products in the family are "product_launch"
             WHEN p.id = f.main_product_id THEN bool_or(product_launch) OVER (PARTITION BY family_id)
	         ELSE product_launch
	       END AS product_launch
	     , f.id AS f_id, f.name, f.main_product_id
	from products as p
	left outer join families as f
	  on p.family_id = f.id
) AS sub
WHERE "enabled"
  AND ((id = main_product_id)	OR (main_product_id is null))

I'm not sure, if this is want you want to get, but try this:

WITH familes_with_one_product_launched AS
(
  SELECT 
      DISTINCT family_id 
  FROM products 
  WHERE
      product_launch = TRUE
), main_products AS
(
	SELECT 
  		p.id 
  	FROM products as p 
  	INNER JOIN families AS f ON (f.main_product_id = p.id)
)
SELECT
	id, name, family_id, enabled,
    CASE WHEN is_main_product AND family_has_launched_product 
    	THEN TRUE
        ELSE product_launch
    END AS product_launch
FROM
(
  SELECT 
      p.*,
      CASE WHEN mp.id IS NULL THEN FALSE ELSE TRUE END AS is_main_product,
      CASE WHEN fl.family_id IS NULL THEN FALSE ELSE TRUE END AS family_has_launched_product
  FROM products AS p
      LEFT JOIN main_products AS mp ON (p.id = mp.id)
      LEFT JOIN familes_with_one_product_launched AS fl ON (p.family_id = fl.family_id)
) AS x

There are some issues with the original table design. The most troublesome is that families and products are mutually referential. The foreign key association from families to products is not declared in the DDL, but is evident in the query. Among the problems caused by this design is the inability to use constraints to ensure that families.main_product_id and products.family_id are mutually consistent. I've refactored the tables by removing main_product_id from families and adding a boolean flag, is_main_product, to products. A partial unique index enforces the constraint that each family can have only one main product.

I've also made the following changes that aren't relevant to the original problem, but improve the model:

1. Added unique constraints to the name columns.
2. Made the boolean columns NOT NULL with defaults.

The changes to the boolean columns help to avoid additional complexity related to handling NULLs when evaluating logic expressions.

Here are the revised table creation statements:

CREATE TABLE families(
  id serial PRIMARY KEY,
  name varchar(50) UNIQUE NOT NULL
);

CREATE TABLE products(
  id serial PRIMARY KEY,
  name varchar(50) UNIQUE NOT NULL,
  family_id int REFERENCES families(id),
  enabled boolean NOT NULL DEFAULT FALSE,
  product_launch boolean NOT NULL DEFAULT FALSE,
  is_main_product boolean NOT NULL DEFAULT FALSE,
  -- a main product must have a family
  CONSTRAINT products_ck CHECK (family_id IS NOT NULL OR is_main_product = FALSE)
);

-- a family can have only one main product
CREATE UNIQUE INDEX products_main_family_product_idx ON products(family_id)
  WHERE is_main_product = TRUE;

Please note, there is an issue caused by using an index to enforce the rule that a family can have only one main product: changing the main product requires two updates, one to set is_main_product to false for the current main product, and a second to set is_main_product to true for the new main product. Since both updates can occur within the same transaction, this should be a minor inconvenience.

I reworked the inserts to eliminate explicit dependencies on generated id values. This helps ensure that id values and the associated sequences are synchronized.

INSERT INTO families(name)(
  VALUES ('macbooks'),
         ('consoles'));

WITH p(
  name,
  family_name,
  enabled,
  product_launch,
  is_main_product
) AS (
  VALUES ('playstation 5', 'consoles', TRUE, TRUE, FALSE),
         ('playstation 3', 'consoles', TRUE, FALSE, TRUE),
         ('xbox 360', 'consoles', TRUE, FALSE, FALSE),
         ('mac book 2015', 'macbooks', TRUE, FALSE, FALSE),
         ('mac book 2018', 'macbooks', TRUE, FALSE, FALSE),
         ('Coca cola', NULL, TRUE, FALSE, FALSE),
         ('Pepsi', NULL, TRUE, FALSE, FALSE))
INSERT INTO products(name, family_id, enabled, product_launch, is_main_product)
SELECT
  p.name,
  f.id,
  p.enabled,
  p.product_launch,
  p.is_main_product
FROM
  p
  LEFT JOIN families f ON f.name = p.family_name;

The following query returns output consistent with the sample posted in the original question:

WITH t AS (
  SELECT
    p.id,
    p.name,
    p.family_id,
    p.enabled,
    -- product_launch is true if any product in the family is the main or a launched product
    BOOL_OR(p.is_main_product OR p.product_launch) OVER (PARTITION BY p.family_id) AS product_launch,
    f.name AS family_name,
    MAX(p.id) FILTER (WHERE p.is_main_product) OVER (PARTITION BY p.family_id) AS main_product_id
  FROM
    products AS p
    LEFT OUTER JOIN families AS f ON p.family_id = f.id
  WHERE
    p.enabled
)
SELECT
  t.*
FROM
  t
WHERE
  -- return only the main product if one is defined for the family
  t.id = COALESCE(t.main_product_id, t.id)
ORDER BY
  t.family_id DESC NULLS LAST,
  t.id;