Aggregating nested set models with field constraints I have a table built on the nested set model that basically records transactions of different natures (defined by the field idx):
pk  lft rgt idx             value
 1   1  30  2783     20402.710000
 2   2   3  2783    -13885.670000
 3   4   5   131        78.547946
 4   6   7  2783      -586.810000
 5   8   9     1         0.013421
 6  10  11  2783     -1777.630000
 7  12  13   873     10791.237266
 8  14  15  2783       -72.510000
 9  16  17  1697       128.626280
10  18  19  2783      -600.430000
11  20  21  1168         6.949175
12  22  23  2783     -2479.080000
13  24  25  1758       853.502787
14  26  27  2783     -1000.580000
15  28  29  3794      3172.428304
16  31  40  2783       615.940000
17  32  33  2783      -615.940000
18  34  39     1         0.040390
19  35  36     1        -0.000152
20  37  38     1        -0.000300
21  41  42  2783      3012.050000


Since these transactions are nested in a particular way where a child node can be of different nature than its parent (representing a sort of &quot;transformation&quot; along the way), I am having a hard time trying to fetch the &quot;balance&quot; for each parent node in their own nature:
prnt_pk      idx            aggr
1           2783        0.000000
3            131       78.547946
5              1        0.013421
7            873    10791.237266
9           1697      128.626280
11          1168        6.949175
13          1758      853.502787
15          3794     3172.428304
16          2783        0.000000
18             1        0.039938
21          2783     3012.050000

How could I achieve this desired output given the original table? I have tried doing something along the lines described in this question, but all I have managed to get was a single parent node and all its children with the same idx, which I could do more easily in my case with SELECT * FROM table WHERE idx = 2783 or similar.
In case it helps, every parent node has a positive value, no node with negative values are allowed to have children.
These are the steps to obtain the desired result:

A cross self-join to obtain every single combination of primary keys possible (this squares the number of records);
Apply the nested set filter (commented below) to remove all combinations of nodes that are not related (e.g., pk 1 and 15);
Assert that only combinations of the same nature are being fetched and aggregated, by equalling the idx from both node and crosst;
Since we are aggregating from crosst.idx, that means it will sum the &quot;balance&quot; for all nodes, including those that are negative (and thus orphans); the third filter ensures we are only taking the total balance starting from original positive parents.

SELECT
    crosst.pk AS prnt_pk,
    crosst.idx,
    SUM(node.value) AS aggr
FROM
    nsm_table AS node,
    nsm_table AS crosst
WHERE 1=1
    AND node.lft BETWEEN crosst.lft AND crosst.rgt --NSM filter for containers
    AND node.idx = crosst.idx
    AND crosst.value &gt; 0
GROUP BY crosst.pk, crosst.idx
HAVING SUM(node.value) &gt;= 0

And the output is exactly as the desired one:
prnt_pk      idx            aggr
1           2783        0.000000
3            131       78.547946
5              1        0.013421
7            873    10791.237266
9           1697      128.626280
11          1168        6.949175
13          1758      853.502787
15          3794     3172.428304
16          2783        0.000000
18             1        0.039938
21          2783     3012.050000