Parent child / recursive - 08-27-2009 , 09:04 AM
I'm banging my head against a brick wall again.
How would I do the following. If my data is
How would I get a list back that shows the parent child relationships.
I'd like a stored proc that given parameter 2 for example, would
Any help would be great.
RE: Parent child / recursive - 08-27-2009 , 10:09 AM
declare @p int;
set @p = 2;
with tmp([from], [to]) as
select @p, @p
select t.[from], t.[to]
from t join tmp on t.[from] = tmp.[to]
select [to] from tmp;
In a real world situation, you need to add code to deal with potential
Re: Parent child / recursive - 08-27-2009 , 10:28 AM
You can use a CTE for this, but depending on the level of recursion and the
needs for extra coding around infinite loops and so forth, a better
solutions might be to use either an adjacency model or nested set theory.
Both of which are very fast and very flexible.
Re: Parent child / recursive - 08-27-2009 , 10:33 AM
On 27 Aug, 16:28, "Rick Sawtell" <r_sawt... (AT) nospam (DOT) hotmail.com> wrote:
Re: Parent child / recursive - 08-27-2009 , 12:23 PM
"Jon" <jonmyates (AT) gmail (DOT) com> wrote
Despite his rantings here about poor to non-existent standards, his books
are well written and show real world application of the concepts discussed.
The adjaceny model as well as nested set theory both have their limitations
and strengths and have far too much detail to go in to here.
Re: Parent child / recursive - 08-29-2009 , 09:39 PM
You can use materialized path to store the relationships, which will make easy the type of query you describe:
If you are using SQL Server 2008 then take a look at HIERARCHYID: