I have this relational data model

product_table

product_source

A table that contains the list of products, with two types: configurable and simple. The simple products are linked to a configurable product. A product_source table that contains the quantities of the different products. I would like to make a query to retrieve all the quantities of the products, in this way: If configurable product, then the quantity is the sum of the quantities of the simple products If simple product, then it is the quantity of the simple product only.

Here is the expected result with the above data:

result

Do you have any idea how to proceed ?

For the moment, I have thought about this type of request, but I don't know how to complete the 'CONFIGURABLE' case

SELECT 
pr.id, 
pr.type, 
pr.configurable_product_id,
CASE
    WHEN (pr.type = 'SIMPLE') THEN ps.quantity
    WHEN (pr.type = 'CONFIGURABLE') THEN (????)
END AS quantity
FROM public."product" as pr
LEFT JOIN public."product_source" as ps
ON ps.product_id = pr.id

CodePudding user response：

You can use window function and partition for calculating sum of quantity (it was like group by)

demo

SELECT 
  pr.id AS product_id,
  CASE
    WHEN (pr.type = 'SIMPLE') THEN ps.quantity
    WHEN (pr.type = 'CONFIGURABLE') THEN SUM(ps.quantity) over ()
  END AS quantity
FROM public."product" as pr
LEFT JOIN public."product_source" as ps
ON ps.product_id = pr.id
ORDER BY pr.id

CodePudding user response：

Your (...) is:

(
  SELECT SUM(ps2.quantity) 
  FROM product as pr2
  LEFT JOIN product_source as ps2
  ON ps2.product_id = pr2.id
  WHERE pr2.configurable_product_id = pr.id
)