Get all ancestors of a child in postgres recursive query

Question

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.

Please provide the create table and insert statements for this data sample (preferably in a fiddle). Never post data as image. — Jim Jones, Jul 09 '21 at 13:54
You may find closure tables easier to use and query - https://stackoverflow.com/a/38701519/5962802 — IVO GELOV, Jul 09 '21 at 14:04

Jim Jones · Accepted Answer · 2021-07-09T18:40:27.930

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

Get all ancestors of a child in postgres recursive query

1 Answers1

Linked