CASE WHEN expression in PostgreSQL-CodePudding

I have two tables "prods"(id, price) and "prods_prices"(id, prod_id, price). It is necessary to display "prods". The minimum price for each product from prods_prices, and if there is none, then 'prods'.'price'. I did it but I think it's wrong.

SELECT DISTINCT "prods"."id" as "id", "prods"."price",
CASE
    WHEN (SELECT min("prods_prices"."price") FROM "prods_prices"  WHERE "prods_prices"."prod_id"=id) isnull THEN "prods"."price"
    ELSE (SELECT min("prods_prices"."price") FROM "prods_prices"  WHERE "prods_prices"."prod_id"=id)
  END 
  AS "price",
FROM "prods"

CodePudding user response：

If id is the primary key on prods, then:

select   prods.id, 
         coalesce(min(prods_prices.price), prods.price) price
from     prods 
left join prods_prices on prods_prices.prod_id = prods.id
group by prods.id

Coalesce returns prods.price if min(prods_prices.price) is null.

CodePudding user response：

With the few informations you gave, you can do the next query (Result here)

with min_price as (select prod_id,min(price) as price from prod_prices group by prod_id)
select p.id,case when mp is null then p.price else mp.price end as price
from prods p left join min_price mp on p.id = mp.prod_id

CodePudding user response：

I have not been able to test it but it should work.

SELECT DISTINCT ON (p.id) p.id, 
  (CASE WHEN pp.price IS NULL THEN p.price ELSE pp.price END) 
FROM prods as p 
LEFT JOIN prods_prices as pp
ON p.id = pp.prod_id
ORDER BY p.id, pp.price;