recursive joins w/o using dynamic sql

Question

recursive joins w/o using dynamic sql

madsnooka

Grasshopper

Points: 23
More actions
August 31, 2010 at 8:34 am

#226427

--drop table #Category
CREATE TABLE #Category(
[CategoryID] [int] IDENTITY(1,1) ,
[ParentID] [int] ,
[CategoryName] [varchar](50)
)
insert into #Category ([ParentID] ,[CategoryName])
select 0, 'parent1'
union all
select 1, 'child1'
union all
select 2, 'child2'
union all
select 3, 'child3'
union all
select 4, 'child4'
union all
select 0, 'parent2'
union all
select 6, 'child1'
union all
select 7, 'child2'
union all
select 8, 'child3'
union all
select 9, 'child4'
select *
from #Category
--output: The user can select any categoryid and the output should
--be the record of the highest level parent or the record w/ parentid=0
--for the given child selected. There are N number of child parent relationships.
----example output
--user selects 2
--output record w categoryid = 1
--user selects 3
--output record w categoryid = 1
--user selects 4
--output record w categoryid = 1
--user selects 6
--output record w categoryid = 6
--user selects 7
--output record w categoryid = 6
--user selects 8
--output record w categoryid = 6
--user selects 9
--output record w categoryid = 6
--etc......

Viewing 5 posts - 1 through 5 (of 5 total)

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

madsnooka Grasshopper Points: 23 More actions · Answer 1

sorry. I didn't provide much of an explanation. I'm trying to write a proc that returns the top level parent record for any child record selected. Is there a way to do this w/o using dynamic sql?

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Thanks for posting a create table script. Not exactly sure what output you need, but a Recursive CTE is a way of avoiding dynamic SQL (bear in mind that it's essentially just a loop, so it's not a true set based solution). I've posted an example using your sample data below:

WITH CTE AS (

SELECT CategoryID, CategoryID AS ParentCategoryID, CategoryName AS ParentCategoryName, 0 AS [Level] FROM #Category C

WHERE ParentID=0

UNION ALL

SELECT c.CategoryID, CTE.ParentCategoryID, CTE.ParentCategoryName, [Level]+1 AS [LEVEL] FROM CTE

INNER JOIN #Category C ON c.ParentID=cte.CategoryID)

SELECT * FROM CTE

ORDER BY CategoryID

This is also a useful reference:

http://msdn.microsoft.com/en-us/library/ms186243.aspx

HowardW One Orange Chip Points: 29109 More actions · Answer 3

Also, a quick question. Is for example child4 truly a child of child3, or is it just a child of parent1? If it's the latter, then the data shouldn't be stored in this way, it's much more efficient to store it's actual parent rather than having to traverse a hierarchy like this to find it's top-level parent. e.g.:

CREATE TABLE #Category(

[CategoryID] [int] IDENTITY(1,1) ,

[ParentID] [int] ,

[CategoryName] [varchar](50)

)

insert into #Category ([ParentID] ,[CategoryName])

select 0, 'parent1'

union all

select 1, 'child1'

union all

select 1, 'child2'

union all

select 1, 'child3'

union all

select 1, 'child4'

union all

select 0, 'parent2'

union all

select 6, 'child1'

union all

select 6, 'child2'

union all

select 6, 'child3'

union all

select 6, 'child4'

select *

from #Category

madsnooka Grasshopper Points: 23 More actions · Answer 4

Is for example child4 truly a child of child3? It is my unterstanding that is the case.

Thanks for the reply. It is exactly what i needed.