I have 2 tables, the first table contains id_product, its rate and its price.

I have an other table which contains a rate and its rate destination if the price for its rate is NULL. Its for always has a price for each product. Here for example, the product 1 doesnt have a price for the rate TSA3. The correspondance table says that if it doesnt have a price for this rate, use the price of TASM4.

So, How can I complete my first table? I think, i need a recursive query, but i dont know how to do it in SQL.

CodePudding user response：

This query gets you the price hirarchically:

with recursive cte(id_product, rate, price, origin_rate) as
(
  select id_product, rate, price, rate
  from mytable
  union all
  select cte.id_product, cte.rate, t.price, t.rate
  from cte
  join map on map.origin_rate = cte.origin_rate
  left join mytable t on t.id_product = cte.id_product
                     and t.rate = map.destination_rate
  where cte.price is null
)
select id_product, rate, price
from cte
where price is not null
order by id_product, rate;