Recursion in mysql filling children from parent, if parent is not null

Question

I have a table made after joining 2 tables, which produces a recursive table. Now I want to add an extra column CalculatedDiscount and fill empty discounts from higher to lower when lower groups are empty. So my idea, theoratically, is to look from the current group to the top: look at the parent group: has value: yes, the CalculatedDiscount is set to his discount. When no: look at his parent group, and so on and on, until at or not at base group.

What I expect is to have that calculated column filled with parents discount.

The ID and ParentID give the hierarchical datastructure. For code and further explanation look at the fiddle: https://dbfiddle.uk/cnSow2zY

without data that produces the result you expect, nobody ca help you, as we can not reproduce the problem — nbk
– nbk, Commented Aug 23, 2023 at 12:20
this is unreadable try dbfiddle.uk/3aMmxpPT add your table with create table and the data as insert and then run the query you have and see if it runs — nbk
– nbk, Commented Aug 23, 2023 at 13:02
As nbk said, this is pretty much untestable for us and hard to understand what you are trying to do. Nevertheless, to maybe give you a pointer: you seem to try to do recursion, but your current cte is not recursive, it is just a left join. A recursive cte has the form "with recursive recname as (select ... from ... union select ... from recname ...)" — Solarflare
– Solarflare, Commented Aug 23, 2023 at 16:07
Have updated my post and put all data and queries in the fiddle — jan-peter Poppe
– jan-peter Poppe, Commented Aug 24, 2023 at 8:41

jan-peter Poppe · Accepted Answer · 2023-08-28 10:53:13Z

0

When rethinking about the question, I came up with the following approach:

When traversing parents in the recursive part of the query, one needed to stop when the discountpercentage of the parent is not Null, so set 'where' on checking cte.discountPercentage (so it is still looking for a value unless having already found a value in the previous iteration). This will result in a cruel-looking table, with resulting rows with same ID, but the names of parentgroups, this is done intentionally because:

Now we can join the resulting cte on itself with an inner join to find where the next id of this "group.ID" has a NOT NULL value for the discountPercentage (but be carefull, if not found until rootnode, it will return nothing so therefore set OR ParentID = 0) make over that result the CASE statement to check wether or not DiscountPercentage is filled: If not: get the selfJoinedTable.discountPercentage (so where ID is nextID) Else: set to null AS calculatedPercentage.

This sounds maybe a bit confusing or complicated, but an overview/ explanation is found in this dbfiddle: https://dbfiddle.uk/kvJ8rIGX

answered Aug 28, 2023 at 10:53

jan-peter Poppe

258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Recursion in mysql filling children from parent, if parent is not null

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related