Get all ancestors of a child in postgres recursive query

I am trying to get all related ancestors of a child with more info by joining with another table. I’m kind of new to backend so recursive cte is hard to understand.

Find SQL Fiddle here

Data I have : product_id,user_id

Data I need :

user_id	master_id	cost
id(john_snow)	null	4
id(bran_stark)	id(john_snow)	6
id(arya_stark)	id(bran_stark)	8
id(sansa_stark)	id(arya_stark)	10

So that I can add profits to respective users and least cost as company profit.

Answer

A RECURSIVE CTE is what you’re looking for. It might look confusing at the first glance, but after a little practice it becomes self-explanatory. In the end, it is just a UNION with slightly different queries:

WITH RECURSIVE get_ancestor(child,parent,cost) AS (
  SELECT r.user_id,r.parent_id,c.cost FROM user_relation r
  JOIN product_cost c ON c.user_id = r.user_id
  UNION 
  SELECT g.child,g.parent,c.cost FROM get_ancestor g
  JOIN user_relation r ON r.user_id = g.child
  JOIN product_cost c ON c.user_id = r.user_id 
)
SELECT * FROM get_ancestor;

Demo: SQL Fiddle