Recursive Select On the table
-
Hai Brothers,
I have table called SAMPLE
It's having 2 Columns Called OBJID BIGINT, PARENTID BIGINT
This above table having the TreeView Objects like parent and child relationship
For Example,
OBJID - 1 as Root Node
OBJID 2 and 3 are child nodes of root node.
OBJID 4,5 and 6 are childs of OBJID 2
OBJID 7 and 8 are childs of OBJID 3
Datas in the table like this
OBJID PARENTID
1 0
2 1
3 1
4 2
5 2
6 2
7 3
8 3
I will give only One Node OBJID.
I want to get the all Childs OBJIDs of the Given OBJID from the table.
The result includes all the childs, grandchilds,... etc upto tail end.
Result may be in the form of string of OBJIDS concatenated by ,(Comma).
or in any form.
One Condition:
We don't use Cursor.
I need a solution for that without using cursor.
Pls Give the Solution.
-
You have two options without a cursor, one for SS2000, the other for SS2005.
SQL Server 2000: You have to use a while loop (or cursor) and a temptable.
SQL Server 2005: Here you can do recursive calls with CTE's (Common Table Expressions)
So, your wanted solution is either: use a while loop and temptable - or go to SQL Server 2005 and use CTE's for true recursive functionality.
There is of course a third alternative - remodel your table(s) and att a column for the materialized path, then you won't need a cursor or loop at all. Though then the cost comes in the form of maintaining that path at all times.
/Kenneth
-
Try the following
CREATE FUNCTION dbo.fnGetNode (
@lNodeId Int --##PARAM @lObjId The parent node for which children will be retrieved.
) RETURNS @tblNode TABLE (
ObjId Int PRIMARY KEY ,
ParentId Int
)
AS
BEGIN
DECLARE @lNextNode Int
SET @lNextNode=0
WHILE @lNextNode IS NOT NULL
BEGIN
SELECT @lNextNode = MIN(ObjId)
FROMdbo.SAMPLE
WHEREParentId = @lNodeId
AND ObjId > @lNextNode
IF @lNextNode IS NOT NULL
BEGIN
INSERT @tblNode ( ObjId, ParentId )
SELECT ObjId, ParentId
FROMSAMPLE
WHEREObjId = @lNextNode
INSERT @tblNode ( ObjId, ParentId )
SELECT ObjId, ParentId
FROMdbo.fnGetNode(@lNextNode)
END
END
RETURN
END
You then simply call the function as follows
SELECT * from dbo.fnGetNode(0)
-
Thanks for your reply brother,
If the OBJIDs are in descending order from parent to child means it's OK.
But it's not in descending order means the code written by you is not working.
So Please check with that.
I am waiting for your reply.
-
Your example suggests that the objids are in descending order.
Why don't you use David's example and check with that yourself? After all, you know your data best.
Using a function this way is no different that using a cursor anyway...
/Kenneth
-
If you use a cursor then you can get locking issues because the table is held open while the cursor runs.
My looping code only locks the record that is selected while the select is going on.
-
David, what I ment is just that some types of functions implements a 'cursory behaviour' under the hood when used in a query. In effect what you get is a row-by-row handling - which is - a cursor.
I just mentioned it because the original poster stated the requirement 'no cursor' - though he/she hasn't explained the reason for that.
/Kenneth
-
Ami,
I didn't take the time to convert this to your table or column names. If you are using INT for your ID's, it's good to 727 levels... usually more than enough for most apps. For BIGINT ID's, only half that level of nesting could be achieved.
The code is self supporting and immediately demonstrable... the temp table is only for the purpose of holding the experimental data instead of taking a chance on overwriting an existing table during the demonstration. I didn't write the code but I use different renditions of it all the time...
--Hierarchy for small tables
--Will blow up if more than 800 levels using 10 digit ID's.
--Good to about 1600 levels when using 5 didig ID's
--Good to 727 levels with 10 digit ID's with a space between each and 1 on each end.
set nocount on
create table #Area (
PageID int,
ParentPageID int,
Descrip varchar(20)
)
insert into #Area values (1,null,'County 1')
insert into #Area values (2,1,'C1 Region 1')
insert into #Area values (3,1,'C1 Region 2')
insert into #Area values (4,2,'C1 R1 Unit 1')
insert into #Area values (5,2,'C1 R1 Unit 2')
insert into #Area values (6,3,'C1 R2 Unit 1')
insert into #Area values (7,null,'County 2')
insert into #Area values (8,7,'C2 Region 1')
insert into #Area values (9,1,'C1 Region 3')
select *, 0 as Level, cast(str(pageid,5) as varchar(8000)) as H into #Temp
from #Area
where ParentPageID is null
declare @lev int
set @lev = 0
while @@rowcount > 0 begin
set @lev = @lev + 1
insert into #Temp
select G.*, @lev, T.H+str(G.pageid,5)
from #Area G join #Temp T
on G.ParentPageID = T.PageID
and T.level = @lev - 1
end
select pageid, parentPageid, LEVEL,LEFT(REPLICATE(' ',Level*2)+descrip,30),h from #temp
order by H
go
drop table #temp
drop table #Area
With a little experimentation on your part, you can qucily develop different SELECT statements to do some remarkable things with it. And, as you can see, it's very simple and short.
There are many other methods to resolve hierarchical information... one method has already been posted on this thread before this message. There are some other great methods for virtually unlimited levels of nesting.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
this should print the tree path :
create procedure ref_id (@id int)
as
begin
declare @ref_id int,
select @ref_id = parent_id
from _table_
where child_id = @id
while @ref_id in (select child_id from _table_)
begin
set @id = @ref_id
select @ref_id = parent_id
from _table_
where child_id = @ref_id
print @id
end
end
amen...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply