Having trouble with recursive CTE query...

Question

Having trouble with recursive CTE query...

sherifffruitfly

Ten Centuries

Points: 1198
More actions
November 25, 2009 at 8:59 pm

#139598

Hi all,
The following is meant to use a CTE to recursively output a base node + ancestors. Running it, I only get some of the ancestors - I'm not sure what's gone wrong. Lil help?
declare @skillname varchar(100)
set @skillname = 'Black Ops';
with SkillAndPrereqs (tID, tName, tVal, Level)
as
(
-- base case
select IT.typeID
, IT.typeName
,TA.valueInt
,0 Level
from dbo.dgmTypeAttributes TA
join dgmAttributeTypes AT on TA.attributeID = AT.attributeID
join dgmAttributeCategories AC on (AT.categoryID = AC.categoryID) and AC.categoryName = 'Required Skills'
join invTypes IT on TA.typeID = IT.typeID
where IT.typeName = @skillname
union all
-- recursive case
select rIT.typeID
, rIT.typeName
,rTA.valueInt
, Level+1
from dbo.dgmTypeAttributes rTA
join dgmAttributeTypes rAT on rTA.attributeID = rAT.attributeID
join dgmAttributeCategories rAC on (rAT.categoryID = rAC.categoryID) and (rAC.categoryName = 'Required Skills')
join invTypes rIT on rTA.typeID = rIT.typeID
join SkillAndPrereqs on (rIT.typeID = SkillAndPrereqs.tVal)
)
select *
from SkillAndPrereqs
order by Level
option (maxrecursion 1000)
This code outputs the following:
tIDtNametValLevel
28656Black Ops115790
28656Black Ops216110
28656Black Ops33270
28656Black Ops40
28656Black Ops40
28656Black Ops50
21611Jump Drive Calibration34561
21611Jump Drive Calibration51
11579Cloaking34261
11579Cloaking51
3456Jump Drive Operation34492
3456Jump Drive Operation34552
3456Jump Drive Operation34022
3456Jump Drive Operation52
3456Jump Drive Operation52
3456Jump Drive Operation52
3455Warp Drive Operation34493
3455Warp Drive Operation13
Note that on the level 0 results, there are tVals of 11579, 21611 and 3327. I expected each of those to be chased down at level 1 of the recursion in the tID column, but 3327 was left out. Why?
Also, I happen to know the data here, and I know that the ancestor chain is not going as deep as it should be (to the bottom). For the above query, here is what the data actually looks like:
I'm pretty sure I'm screwing up something fairly simple (boundary condition or something mebbe?), but I can't quite see what it is. Any idea?
Thanks!
cdj

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Is it stopping early or skipping levels of ancestors? Possibly a join isn't finding a match? We'd need to see source data in all the tables to know for sure.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

sherifffruitfly Ten Centuries Points: 1198 More actions · Answer 2

Bob Hovious 24601 (11/26/2009)
Is it stopping early or skipping levels of ancestors? Possibly a join isn't finding a match? We'd need to see source data in all the tables to know for sure.

Yah, it's possible rows are being tossed out somewhere on the join path. I'm new to cte recursions, so I thought/hoped there was just something wrong with the formulation of the query itself. I'll check the intermediate tables to verify the proper associated records exist.

Thanks!

cdj