recursive query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

recursive query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-10 : 08:48:00

James writes "An expert on this forum said ---

When you do a recursive join, you're joining the table to itself. To keep the query processor from getting confused, you need to use an 'alias' on the table.

Here's the T-SQL syntax for the type of query you described above.

SELECT foo
FROM Message M1 <-- 'M1' is the aliased name of the table
JOIN Message M2 <-- again, aliasing the message table,
now as 'M2'
ON M1.PostID = M2.ParentPostID
WHERE blah

That's all there is to it. The query processor treats M1 and M2 as two separate tables. You can also use:

SELECT foo
FROM Message AS M1
JOIN Message AS M2
ON M1.PostID = M2.ParentPostID
WHERE blah

-------

But this solution is only good for slecting all the rows in the table, and as soon as you apply a where clause you only get one-level of rows back.

For example, if you narrow the search to:

where m2.ParentPostID = 1

How do I get all the PostIDs back for a ParentPostID and its sub PostIDs.

James"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-10 : 09:45:37

Do an artical (or forum) search on this site for "trees" and/or "hierarchy". Check out the articles AND comments.

But here is an example of working with hierarchies. The basic idea is to apply your join above once for each level if nesting.


set nocount on
declare @items		Table (itemid int identity(1,1), itemName varchar(10))
declare @association	Table (itemid int, parentitemid int)
declare @tree		Table (parentitemid int, itemid int, lev int, struct varchar(200))

declare @lev int
set @lev = 0

insert @items (itemname)
select 'item01' union
select 'item02' union
select 'item03' union
select 'item04' union
select 'item05' union
select 'item06' union
select 'item07' union
select 'item08' union
select 'item09' union
select 'item10' union
select 'item11' union
select 'item12'
order by 1

insert @association
select 1,null union 
select 2,1 union
select 3,1 union
select 4,3 union
select 5,4 union
select 6,2 union
select 7,2 union
select 8,6 union
select 9,6 union
select 10,2 union
select 11,10 union
select 12, 1

--get the root nodes
insert @tree
select null, a.itemid, @lev, itemname
from	@association a
JOIN	@items i ON i.itemid = a.itemid
where	parentitemid is NULL


--while children exist for current parent level
while @@Rowcount > 0
Begin
	set @lev = @lev + 1

	insert @tree
	select t.itemid, a.itemid, @lev, isNull(t.struct+'.','') + itemname
	from	@association a
	JOIN	@items i 
			ON i.itemid = a.itemid
	JOIN	@tree t 
			ON t.itemid = a.parentitemid
			and t.lev = @lev - 1

	--avoid circular references
	Left JOIN @tree excl
			ON excl.parentitemid = a.itemid
	where	excl.itemid is NULL
End

select * from @tree order by itemid

select	replicate(char(9), lev) + 
	 convert(varchar,i.itemid) + '-' + 
	 itemname
From	@tree t
JOIN	@items i ON t.itemid = i.itemid
Order by i.itemid

output:

@tree rows
parentitemid itemid      lev         struct                                                                                                                                                                                                   
------------ ----------- ----------- --------------------------------
NULL         1           0           item01
1            2           1           item01.item02
1            3           1           item01.item03
3            4           2           item01.item03.item04
4            5           3           item01.item03.item04.item05
2            6           2           item01.item02.item06
2            7           2           item01.item02.item07
6            8           3           item01.item02.item06.item08
6            9           3           item01.item02.item06.item09
2            10          2           item01.item02.item10
10           11          3           item01.item02.item10.item11
1            12          1           item01.item12


Dispay of @tree                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
----------------------------------------------------------------------
1-item01
	2-item02
	3-item03
		4-item04
			5-item05
		6-item06
		7-item07
			8-item08
			9-item09
		10-item10
			11-item11
	12-item12

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-13 : 03:23:27

Also refer
http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources