the node with no limit !

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kylin - 29 Mar 2005 03:38 GMT

I user one table for ProductSort ,and the fields like this-
ID|SortName|Notes|ParentID|
And if the one sort node have a son node ,
I let the son's ParentID=Parent's ID................

Now I input a ID value ,
and first ,I judge if the ID has a son node ,
if has no ,
select * from Product where ProductSortId=@ID,
if has,
judge agin the son node has so node too,
untill there is no son node .
then
select * from Product where ProductSortId=@ID

how can I come true about this In T-SQL ?

Reply to this Message

Sylvain Lafontaine - 29 Mar 2005 06:43 GMT

The easiest way to do this would be to create a stored procedure and use a
cursor to travel your hierarchy. You can also hide this recursive call
inside a function (UDF - User Defined Function). You will find of lot of
examples for recursive calls on the internet.

Another solution is to store the list of all the sons in a single varchar
field for each row by separating them with a point as in: 1.5.8.3.... (in
this example, the first son has the ID of 1; the second, the ID of 5, etc.
The desired value for all the rows is very simple to retrieve with a simple
LIKE in a single select statement but this method requires that you update
all the other rows when the hierarchy is changed.

S. L.

>I user one table for ProductSort ,and the fields like this-
> ID|SortName|Notes|ParentID|
[quoted text clipped - 12 lines]
>
> how can I come true about this In T-SQL ?

Reply to this Message

kylin - 30 Mar 2005 01:59 GMT

Thanks a lot !

> The easiest way to do this would be to create a stored procedure and use a
> cursor to travel your hierarchy. You can also hide this recursive call
[quoted text clipped - 26 lines]
> >
> > how can I come true about this In T-SQL ?

Reply to this Message