sql - Query to fetch all referenced entities recursively -

i have datamodels consists of 'claims' (to make things simple stackoverflow) has openamount field. there 2 other tables, 'claimcoupling' , 'claimentryreference'.

the claimcoupling table directly references claim table , claimentryreference booking of received amount can booked on multiple claims (see claimentry_id). see diagram;

for simplicity i've removed amounts that's not struggling with.

what want query start @ claim table, , fetches claim openamount <> 0. want able print out accurate report of how openamount came be, means i'll need print out claims coupled claim. make more interesting same thing applies bookings, if booking made on claim x , claim y , x has open amount want fetch both x , y can show payment booked whole.

i've attempted recursive cte (rightfully) blows on circulair references. figured i'd fix simple statement recursively add records not yet part of cte not allowed....

    coupledclaims (     --get unique combinations      select cc.substractedfromclaim_id claim_id,            cc.addedtoclaim_id linked_claim_id dbo.claimcoupling cc     union     select cc.addedtoclaim_id claim_id,            cc.substractedfromclaim_id linked_claim_id dbo.claimcoupling cc ), myclaims (   select * claim openamount <> 0   union   select c.* coupledclaims join myclaims mc on coupledclaims.claim_id = mc.id join claim c on c.id = coupledclaims.linked_claim_id   c.id not in (select id myclaims) ) select * myclaims 

after fiddling around way long decided i'd actual loop... @@rowcount , manually add them table variable writing solution (which i'm sure can work) figured i'd ask here first because don't writing loops in tsql feel it's ugly , inefficient.

see following sql fiddle data models , test data (i commented out recursive part otherwise not allowed create link);

http://sqlfiddle.com/#!6/129ad5/7/0

i'm hoping here have great way of handling problem (likely i'm doing wrong recursive cte). completion done on ms sql 2016.

so here i've learned , done far. comment of habo refers following question; infinite loop in cte when parsing self-referencing table

firstly decided @ least 'solve' problem , wrote manual recursion, solves problem not 'pretty' cte solution hoping/thinking easier read out perform manual recursion solution.

manual recursion

/****************************/ /* claims , payment logic */ /****************************/ declare @rows int = 0 declare @relevantclaimids table( debtor_id int, claim_id int ) set nocount on  --get anchor condition insert @relevantclaimids (debtor_id, claim_id) select debtor_id, id claim c openamount <> 0  --do recursion while @rows <> (select count(*) @relevantclaimids) begin set @rows = (select count(*) @relevantclaimids)  --subtracted insert @relevantclaimids (debtor_id, claim_id) select distinct c.debtor_id, c.id claim c inner join claimcoupling cc on cc.substractedfromclaim_id = c.id join @relevantclaimids rci on rci.claim_id = cc.addedtoclaim_id --might multiple paths recursion eliminate duplicates left join @relevantclaimids dup on dup.claim_id = c.id dup.claim_id null  --added insert @relevantclaimids (debtor_id, claim_id) select distinct c.debtor_id, c.id claim c inner join claimcoupling cc on cc.addedtoclaim_id = c.id join @relevantclaimids rci on rci.claim_id = cc.substractedfromclaim_id --might multiple paths recursion eliminate duplicates left join @relevantclaimids dup on dup.claim_id = c.id dup.claim_id null  --payments insert @relevantclaimids (debtor_id, claim_id) select distinct c.debtor_id, c.id @relevantclaimids f join claimentryreference cer on f.claim_id = cer.claim_id join claimentryreference cer_linked on cer.claimentry_id = cer_linked.claimentry_id , cer.id <> cer_linked.id join claim c on c.id = cer_linked.claim_id --might multiple paths recursion eliminate duplicates left join @relevantclaimids dup on dup.claim_id = c.id dup.claim_id null end 

then after received , read comment decided try cte solution looks this;

cte recursion

with tree         (         select debtor_id, id claim_id, cast(id varchar(max)) levels         claim c         openamount <> 0          union         select c.debtor_id, c.id, t.levels + ',' + cast(c.id varchar(max)) levels         claim c         inner join claimcoupling cc on cc.substractedfromclaim_id = c.id         join tree t on t.claim_id = cc.addedtoclaim_id         (','+t.levels+',' not '%,'+cast(c.id varchar(max))+',%')          union         select c.debtor_id, c.id, t.levels + ',' + cast(c.id varchar(max)) levels         claim c         inner join claimcoupling cc on cc.addedtoclaim_id = c.id         join tree t on t.claim_id = cc.substractedfromclaim_id         (','+t.levels+',' not '%,'+cast(c.id varchar(max))+',%')          union         select c.debtor_id, c.id, t.levels + ',' + cast(c.id varchar(max)) levels         tree t         join claimentryreference cer on t.claim_id = cer.claim_id         join claimentryreference cer_linked on cer.claimentry_id = cer_linked.claimentry_id , cer.id <> cer_linked.id         join claim c on c.id = cer_linked.claim_id         (','+t.levels+',' not '%,'+cast(c.id varchar(max))+',%')         ) select  distinct tree.debtor_id, tree.claim_id tree 

this solution indeed lot 'shorter' , easier on eyes perform better?

performance differences

manual; cpu 16, reads 1793, duration 13

cte; cpu 47, reads 4001, duration 48

conclusion

not sure if it's due varchar cast required in cte solution or has 1 iteration before completing it's recursion requires more resources on fronts manual recursion.

in end possible cte looks aren't (thank god ;-)) performance wise sticking manual recursion seems better route.