Here I have a BOM problem. The data in a tree format:
1 (value:100) -> 2 (value: 2) -> 4 (value: 1)
-> 5 (value: 10) -> 6 (value: 1)
-> 3 (value: 1)
Each item's value can be obtained by following either of two rules:
- get the item by its value e.g. here item 5 has value 10
- get the item by the total sum of its children (instead of by its value) e.g. here item 5 will be the value of its child (item 5 will get the value of item 6, i.e. value 1).
So how would I build the value of item 1 based on the above rules? Item 1 can cost just 3.
I am familiar with recursion in SQL, however I am not sure how to extend my answer to my specific case. I can also change the data format, however I cannot think of another data format which would help.
The following is an example of traversal with my data, still this does not give me what I want i.e. cheapest cost for item 1.
with recursive tree_data as
(
select 1 parent_id, 2 child_id, 2 child_cost
union
select 1 parent_id, 3 child_id, 1 child_cost
union
select 2 parent_id, 4 child_id, 1 child_cost
union
select 2 parent_id, 4 child_id, 1 child_cost
union
select 2 parent_id, 5 child_id, 10 child_cost
union
select 5 parent_id, 6 child_id, 1 child_cost
), traversal as
(
select *, 0 node_cost
from tree_data
where parent_id = 1
union
select tree_data.*, traversal.node_cost + tree_data.child_cost
from tree_data
inner join traversal on tree_data.parent_id = traversal.child_id
)
select * from traversal
To be clear, a variant of this output would be of interest. Even just solving this for the root would be interesting (i.e. show only line 1).
| parent_id | cheapest_value | actual_value |
|---|---|---|
| 1 | 3 | 100 |
| 2 | 2 | 2 |
| 3 | 1 | 1 |
| 4 | 1 | 1 |
| 5 | 1 | 10 |
| 6 | 1 | 1 |