Recursive Query (Bradcrumb trial-like)

Question

Recursive Query (Bradcrumb trial-like)

Trystan Hughes

SSCrazy

Points: 2109
More actions
May 24, 2006 at 3:41 am

#114253

Hi all,
I have the requirements to get the path of a particular location through getting its Parent ID to get to that Parent Element, then do the same on that Parenet Element to get its parent element etc etc.
I am currently doing this through issueing a query each time I get the new element, so if I have a location element that has 7 tiers, then effectively there will be 7 database queries/calls. Ideally I want to be able to wrap this up in one nice recursive query. Is this at all possible?
Thanks in advance.
Tryst

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

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

SQLZ SSChampion Points: 12872 More actions · Answer 1

Tryst,

You can do this using recursive calls to a stored procedure but be aware that a procedure has a limit of 32 nested calls to itself.

A much better way of achieving this though is described in this msdn article on how to expand a parent-child hierarchy without needing to use recursion.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_5yk3.asp

The only thing to note is that the above example starts of with a parent, and finds all descendents and it seems you want to do the reverse but hopefully you should be able to engineer a similar solution that achieves the opposite.

Hope that helps,

Karl
source control for SQL Server

RyanRandall SSChampion Points: 13623 More actions · Answer 2

Hi all,

Tryst - You might want to consider storing the lineage (breadcrumb) in a column in your table. These articles endorse that and show you how to do it (again using a while loop as in the link grambowk posted)...

http://www.sqlteam.com/item.asp?ItemID=8866

http://www.thesitedoctor.co.uk/SeventhNight/TreeStructs/

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

stax68 SSChampion Points: 11711 More actions · Answer 3

Based on the sample data from one of the above examples, this is a rough-and-ready outline of teh kind of code you could use. Obviously needs tidying up, but you get the idea.

--etst data

declare

@hierarchy table(parent varchar(20),child varchar(20))

insert

@hierarchy select 'World','Europe' union all select 'World','North America'

union

all select 'Europe','France' union all select 'France','Paris'

union

all select 'North America','United States' union all select 'North America','Canada'

union

all select 'United States','New York' union all select 'United States','Washington'

union

all select 'New York','New York City' union all select 'Washington','Redmond'

----input interface stub

declare

@startelement

varchar(20)

,

@delimiter varchar(10)

select

@startelement = 'New York City', @delimiter = '\'

--loop

declare

@path varchar(8000)

select

@path = ''

while

@@rowcount > 0

begin

select @path = @delimiter + @startelement + @path

select @startelement = parent from @hierarchy where child = @startelement

end

select

@path

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 4

Hi, and thanks for the replies people.

One issue, I am to run this query on a handheld device against SQL CE 3, which doesn't allow stored proc's i believe. I guess I can use a function that includes a query that recalls itself.

Thanks

Tryst