April 8, 2008 at 8:46 am
Hello guys,
I have a table:
ID, ParentID
1 0
2 0
3 1
4 1
5 3
6 3
7 5
8 7
....
As you can see, any node has a parent node. So the whole scenario is like a tree.
Now I need a query to get the whole tree by passing a node's id, for example, given ID = 1, I need to return result:
1, 3, 4, 5, 6, 7, 8,... all the nodes trace back to node ID 1.
How do I do this?
Thanks.
April 8, 2008 at 8:59 am
This is a hierarchy and you'd need some recursion to get everything. Or a loop.
Check this out: http://www.sqlservercentral.com/Forums/Topic437859-338-1.aspx
April 8, 2008 at 11:32 am
Hello,
Thanks for your reply, but I spent some time on this and I found it is not difficult or complex at all, just use some simple recursive algorithm and temp tables and there you go.
Here is the code:
CREATE Proc spGetAllSubNodeByOrg(@OrgID int)
AS
if not exists (select * from dbo.sysobjects where id = object_id('dbo.Node') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
--Table Node is a temp table
Create Table Node
(
ID int
)
End
if not exists (select * from dbo.sysobjects where id = object_id('dbo.Result') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
--Table Result is for keeping result
Create Table Result
(
ID int
)
End
Insert into Result(ID) Values (@OrgID)
Insert into Node Select ID From Org Where ParentID = @OrgID
If not exists (Select * From Node) Select * From Result Order By ID
else
Begin
Select Top 1 @OrgID = ID From Node
Delete From Node Where ID = @OrgID
exec spGetAllSubNodeByOrg @OrgID
End
Drop Table Result
Drop Table Node
GO
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply