Recursive Queries - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Recursive Queries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-10-28 : 08:09:55

Brett writes "On 12-08-99, you attempted to answer a question on recursive queries. You gave an answer, but not the answer to the question that was asked.

The guy was talking about a table that might have entries such as


PostID    ParentPostID   Author   Subject
1                        User1    Problem
2         1              User2    Re: Problem
3                        User3    Different Problem
4         1              User4    Re: Problem
5         2              User5    Re: Re: Problem
6         3              User6    Re: Different Problem
7         4              User1    Re: Problem - Fixed

He wants a single query that, given the PostID of 1, will return the result set:


PostID    ParentPostID   Author   Subject
1                        User1    Problem
2         1              User2    Re: Problem
4         1              User4    Re: Problem
5         2              User5    Re: Re: Problem
7         4              User1    Re: Problem - Fixed

Oracle provides a way to do this using the CONNECT BY and START WITH clauses, such as


SELECT PostID, ParentPostID, Author, Subject
  FROM Message
CONNECT BY PRIOR PostID = ParentPostID
 START WITH PostID = 1;

This is an Oracle specific extension to standard SQL. DB2 has a totally different way of achieving a similar thing which is a lot more long-winded and I won't go into here, but IBM's website has a good article on coverting Oracle recursive SQL to DB2.

The simple JOIN that you provided in your answer will not return all of the above rows, or at the very least I don't see how. Recursive SQL is not a simple join. The database engine may very well implement it using joins internally, but the joins are done with the table and the rows it's already got in the result set, and this will be done again and again recursively until we aren't adding any more rows.

I doubt MS Access would provide recursive SQL support, but I have seen a few things on the net referring to recursive SQL in SQL Server, but I've yet to see some good reference material or an example query, yet."

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-28 : 08:48:19

and the point of recursion in sql is???

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-28 : 09:53:58

UDF's make this fairly easy. Here's one example:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25964

By definition, you need to loop through the set of rows in the table as many times as there are levels of depth. But it still can be done as a standard, set-based efficient T-SQL method, as I demonstrate.

Do you have a more specific question? Sorry you were not satisfied with the answer to a question asked back in 1999, I was not here then! Have you been waiting 5 years to get your question answered?

- Jeff

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-29 : 13:01:30

I created a simple select for this and it worked

Select * from @post a where postid = 1 or parentpostid =1 or parentpostid in
(
    select postid from @post  where parentpostid =1)

but when I added more data with more levels it didn't, so I created a loop. The solution is very much
like jeff's but with less variables

Declare @post table
(
    PostID          int,  
    ParentPostID    int,
    Author          nvarchar(5),
    Subject         nvarchar(50)
)
--Data with more levels
insert into @post
select 1,null,'User1', 'Problem' union
select 2, 1, 'User2', 'Re: Problem' union
select 3, null, 'User3', 'Different Problem' union
select 4, 1, 'User4', 'Re: Problem' union
select 5, 2, 'User5', 'Re: Re: Problem' union
select 6, 5, 'User5', 'Re: Re: Re: Problem' union
select 7, 6, 'User5', 'Re: Re: Re: Problem' union
select 8, 7, 'User5', 'Re: Re: Re: Re: Problem' union
select 9, 8, 'User5', 'Re: Re: Re: Re: Re: Problem' union
select 10, 9, 'User5', 'Re: Re: Re: Re: Re: Re: Problem' union
select 11, 10, 'User5', 'Re: Re: Re: Re: Re: Re: Re: Problem' union
select 12, 3, 'User6', 'Re: Different Problem' union
select 13, 4, 'User1', 'Re: Problem - Fixed'

Solution


declare @postID int
declare @results table
(
    PostID          int,  
    ParentPostID    int,
    Author          nvarchar(5),
    Subject         nvarchar(50)
)

set @postID = 1

insert into @results
select * from @post p
Where postID=@postID 

While @@rowcount>0
begin
    insert into @results
    select * from @post p Where exists 
    (
        select 1 from @results r Where p.parentpostid = r.postid 
    ) 
    and not exists 
    (
        select 1 from @results r Where p.postid = r.postid
    )
end

Results

PostID      ParentPostID Author Subject                                            
----------- ------------ ------ -------------------------------------------------- 
1           NULL         User1  Problem
2           1            User2  Re: Problem
4           1            User4  Re: Problem
5           2            User5  Re: Re: Problem
13          4            User1  Re: Problem - Fixed
6           5            User5  Re: Re: Re: Problem
7           6            User5  Re: Re: Re: Problem
8           7            User5  Re: Re: Re: Re: Problem
9           8            User5  Re: Re: Re: Re: Re: Problem
10          9            User5  Re: Re: Re: Re: Re: Re: Problem
11          10           User5  Re: Re: Re: Re: Re: Re: Re: Problem

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-29 : 14:00:02

quote:
Originally posted by AskSQLTeam

Brett writes "On 12-08-99, you attempted to answer a question on recursive queries.

OK, hold the phone...

Got your hands on a copy of 2005 yet?

And recirsion DOES NOT occur in DB2 For the maineframe (At least that's V7 and earlier)

And yes ORACLE CONNECT BY is WAY Cool...

Know what else is cool....Nested Tables.....

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources