Nested sum loop until foreign key 'dies out'

Question

I am pulling my hair out over a data retrieval function I'm trying to write. In essence this query is meant to SUM up the count of all voorwerpnummers in the Voorwerp_in_Rubriek table, grouped by their rubrieknummer gathered from Rubriek.
After that I want to keep looping through the sum in order to get to their 'top level parent'. Rubriek has a foreign key reference to itself with a 'hoofdrubriek', this would be easier seen as it's parent in a category tree.
This also means they can be nested. A value of 'NULL' in the hoofdcategory column means that it is a top-level parent. The idea behind this query is to SUM up the count of voorwerpnummers in Voorwerp_in_rubriek, and add them together until they are at their 'top level parent'.
As the database and testdata is quite massive I've decided not to add direct code to this question but a link to a dbfiddle instead so there's more structure.

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=8068a52da6a29afffe6dc793398f0998

I got it working in some degree using this query:

SELECT  R2.hoofdrubriek ,
        COUNT(Vr.rubrieknummer) AS aantal
FROM    Rubriek R1
        RIGHT OUTER JOIN Rubriek R2 ON R1.rubrieknummer = R2.hoofdrubriek
        INNER JOIN Voorwerp_in_rubriek Vr ON R2.rubrieknummer = Vr.rubrieknummer
WHERE   NOT EXISTS ( SELECT *
                     FROM   Rubriek
                     WHERE  hoofdrubriek = R2.rubrieknummer )
        AND R1.hoofdrubriek IS NOT NULL
GROUP BY Vr.rubrieknummer ,
        R2.hoofdrubriek

But that doesn't get back all items and flops in general. I hope someone can help me.

You can write a recursive query to walk down your record tree, see https://stackoverflow.com/questions/959804/simulation-of-connect-by-prior-of-oracle-in-sql-server - you basically have a `WITH` that is two part, connected with a union, one cites the starting condition (`where hoofdcategory is null`) and the other part cites a query that joins the cte to itself (thereby establishing a recursion) — Caius Jard, Jun 04 '19 at 09:24
@CaiusJard This works like a charm, thank you very much for helping me onto the right path. — Lars, Jun 04 '19 at 10:13

score 0 · Answer 1 · answered Jun 04 '19 at 09:59

If I got it right

declare @t table (
rubrieknummer int,
cnt int);

INSERT @t(rubrieknummer, cnt)
SELECT R.rubrieknummer, COUNT(Vr.voorwerpnummer) 
FROM Rubriek R 
INNER JOIN voorwerp_in_rubriek Vr ON R.rubrieknummer = Vr.rubrieknummer 
GROUP BY Vr.rubrieknummer, R.rubrieknummer;

--select * from @t;

with t as(
  select rubrieknummer, cnt
  from @t
  union all
  select r.hoofdrubriek, cnt
  from t
  join Rubriek r on t.rubrieknummer = r.rubrieknummer
)
select rubrieknummer, sum(cnt) cnt  
from t
group by rubrieknummer;

applying to your fiddle data returns

rubrieknummer   cnt
<null>  42
100 42
101 26
102 6
103 10
10000   8
10100   4
10101   1
10102   3
10500   4
10501   2
10502   2
15000   18
15100   6
15101   2
15102   2
15103   2
15500   12
15501   4
15502   3
15503   5
20000   6
20001   2
20002   1
20003   1
20004   2
25000   4
25001   1
25002   1
25003   1
25004   1
30001   2
30002   1
30004   3

Nested sum loop until foreign key 'dies out'

1 Answers1