the node with no limit !

[kylin]

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 ?

 

[Sylvain Lafontaine]

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.

 

[kylin]

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
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.