Ah, nothing like recursive sql.

The principal is going to sort of the same but backwards. In a nut shell, here's what you can do. Let me first disclaim that I'm not a SQL expert so there may be a MUCH easier or at least more efficient way to do this. I'll assume that you want to sort this list by the item name.

You start with a temp table:
ItemID INT, ParentItemID INT, Level INT, ItemName VARCHAR, SortString VARCHAR
(SortString will need to be pretty long, you'll see why)

<sql>
SET @level=0

--Select all the root items from the table (based on whatever criteria deems a item to be "root", like ParentItemID = NULL). Select them like this:
INSERT INTO #itemTree
    SELECT ItemID, ParentItemID, @level, ItemName, ItemName

WHILE @@ROWCOUNT>0
BEGIN
    SET @level=@level+1
    INSERT INTO #itemTree
        SELECT ItemID, ParentItemID, @level, ItemName, SortString+ItemName
        FROM MyItems AS t1 JOIN #itemTree AS t2 ON t1.ParentItemID=t2.ItemID
        WHERE t2.Level=(@level-1)
END

SELECT ItemID, ParentID, Level, ItemName
FROM #itemTree
ORDER BY SortString
</sql>

The key to this is the SortString. You'll end up with a temp table with a Level and SortString column like this...
0 RootItem1
0 RootItem2
1 RootItem1ItemA
1 RootItem1ItemB
1 RootItem2ItemA
1 RootItem2ItemB
2 RootItem1ItemAChild1
2 RootItem1ItemAChild2
2 RootItem1ItemBChild1
2 RootItem1ItemBChild2
2 RootItem2ItemAChild1
2 RootItem2ItemAChild2
2 RootItem2ItemBChild1
2 RootItem2ItemBChild2

Then thru the natural sorting order on the final select, you get:
0 RootItem1
1 RootItem1ItemA
2 RootItem1ItemAChild1
2 RootItem1ItemAChild2
1 RootItem1ItemB
2 RootItem1ItemBChild1
2 RootItem1ItemBChild2
0 RootItem2
1 RootItem2ItemA
2 RootItem2ItemAChild1
2 RootItem2ItemAChild2
1 RootItem2ItemB
2 RootItem2ItemBChild1
2 RootItem2ItemBChild2

Peter
------------------------------------------------------
Work smarter, not harder.

ahh yes, there truly is nothing like recursive sql, it brings on the brain pain :D well, peter, once more, looks great, i'm definately gonna try this, i tell ya what this is gonna help out a bunch. There's truly nothing like learning t-sql on the fly! thanks a bunch, i'll give you some feedback on the progress!

Flyin

interesting.. Not sure i need that much info returned, all i really need is the similar result as in if the root categoryID = 3 then return back a string of all the subcategories that belong to 3 and all of it's subcategories subcategories and so on and so on.

Example

Politics 3
Bush 12
War 22
Clinton, 49
Lewinski 94

in this list, rootcategory is politics,
bush is child to politics,
war is child to bush,
clinton is is child to politics,
lewinski is child to clinton.

I really just need to be returned

3,12, 22, 49, 94

any more ideas?

BTW, do you have a good reference guide that helps you with these stored procs because i don't use them enough due to the fact that i don't know anything about them! thanks

No, no good reference, just an old SQL book, the SQL books online, and my imagination.

If you were to modify the SPROC to accept a start point for your tree, you could easily have a single SPROC that would allow you to get the whole tree or any one branch of the tree...

<sql>
CREATE PROCEDURE getItemTree @nStartItem INT
AS
    SET @level=0

    --Select all the root items from the table (based on whatever criteria deems a item to be "root", like ParentItemID = NULL). Select them like this:
    INSERT INTO #itemTree
        SELECT ItemID, ParentItemID, @level, ItemName, ItemName
        WHERE ParentItemID = @nStartItem
        --If you pass NULL and a 'root' item's parent is NULL,
        -- you'll get the whole tree

    WHILE @@ROWCOUNT>0
    BEGIN
        SET @level=@level+1
        INSERT INTO #itemTree
            SELECT ItemID, ParentItemID, @level, ItemName, SortString+ItemName
            FROM MyItems AS t1 JOIN #itemTree AS t2 ON t1.ParentItemID=t2.ItemID
            WHERE t2.Level=(@level-1)
    END

    SELECT ItemID, ParentID, Level, ItemName
    FROM #itemTree
    ORDER BY SortString
GO
</sql>


Peter
------------------------------------------------------
Work smarter, not harder.