Hello all,

I'm banging my head against a brick wall again.

How would I do the following. If my data is

From To
1 2
2 3
3 4
5 NULL
6 7

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
return:

2
3
4

Any help would be great.

Thanks,

Jon

declare @p int;
set @p = 2;
with tmp([from], [to]) as
(
select @p, @p
union all
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
infinite loop.

Linchi

"Jon" wrote:

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.


Rick Sawtell

On 27 Aug, 16:28, "Rick Sawtell" <r_sawt... (AT) nospam (DOT) hotmail.com> wrote:
Rick, have any examples of either?

"Jon" <jonmyates (AT) gmail (DOT) com> wrote

Take a look at Joe Celko's book on Trees and Hierarchies.

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.

Rick Sawtell

You can use materialized path to store the relationships, which will make easy the type of query you describe:
http://www.dbazine.com/oracle/or-articles/tropashko4

If you are using SQL Server 2008 then take a look at HIERARCHYID:
http://msdn.microsoft.com/en-us/magazine/cc794278.aspx
http://technet.microsoft.com/en-us/l.../bb677290.aspx

--
Plamen Ratchev
http://www.SQLStudio.com