Recursive Select I think!! - 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 Select I think!!

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-30 : 09:22:42

Kuldip writes "I am creating a website with a message board system on it (Just to let you know) Here is the question, I have one table for all of the messages I can get a stored procedure to do the following


Message |
        |-> Reply
----------------------------------------------------
Message

But what I want to be able to do is have the following kind of view / structure


Message |
        |-> Reply |
                  |-> Reply |
                            | -> and So On.....
----------------------------------------------------
Message |
        |-> Reply

Here is the current Table Structure


Id	int	
BoardID	int	
MParent	int	
UserID	int	
_Date	datetime
Title	varchar	
Body	varchar

Any Suggestions
Thanks Kuldip"

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-30 : 11:23:44

This isn't code for you just an example of how to do something like this ...

This code will create a tree ... sort of ... it just gets all children under a certain node ... in this instance it returns all children of the root nodes... if you structure is something like this this code should be pretty easy for you to manipulate for your needs.


declare @tree table (parent_id int, child_id int, value varchar(256), level int)
declare @level int

set @level = 0

insert into @tree (parent_id, child_id, value, level)
    select null,
           catalog_category_id,
           category_name,
           @level
      from catalog_categories
     where parent_id is null

while exists(select catalog_category_id
               from catalog_categories 
              where parent_id in (select child_id
                                    from @tree)
               and catalog_category_id not in (select child_id
                                                 from @tree))
    begin
        set @level = @level + 1

        insert into @tree (parent_id, child_id, value, level)
            select parent_id,
                   catalog_category_id,
                   category_name,
                   @level
              from catalog_categories
             where parent_id in (select child_id
                                   from @tree)
               and catalog_category_id not in (select child_id
                                                 from @tree)
    end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources