>Follow-up...again, given the following structure (which could be hierarchical menu options, or any similar situation)
>
>

>DECLARE @tProducts TABLE (ID int, Name char(50), ParentID int)
>
>INSERT INTO @tProducts VALUES (1, 'Brand 1', null)
>INSERT INTO @tProducts VALUES (2, 'Brand 2', null)
>INSERT INTO @tProducts VALUES (3, 'Brand 3', null)
>
>
>INSERT INTO @tProducts VALUES (6, 'Brand 1, Group 1', 1)
>INSERT INTO @tProducts VALUES (7, 'Brand 1, Group 2', 1)
>INSERT INTO @tProducts VALUES (8, 'Brand 1, Group 3', 1)
>
>INSERT INTO @tProducts VALUES ( 9, 'Brand 2, Group 1', 2)
>INSERT INTO @tProducts VALUES (10, 'Brand 2, Group 2', 2)
>
>INSERT INTO @tProducts VALUES ( 11, 'Brand 3, Group 3', 3)
>
>INSERT INTO @tProducts VALUES (12, 'Brand 1, Group 1, Item 1', 6)
>INSERT INTO @tProducts VALUES (13, 'Brand 1, Group 1, Item 2', 6)
>
>INSERT INTO @tProducts   VALUES (14, 'Brand 1, Group 1, Item 1, SKU 1', 12)
>

>
>Now, suppose that for a particular row, you wanted to know all parents, or all children.

I'll come up with how to do it without recursive CTE's later. Just to point out that I already agreed upon this being the strenght of the CTE's in your previous hierarchical example. See my first comments about this in 2004 relating to Celko's solution handling such a problem (see link at the bottom) Re: SLCFox: Joe Celko Thread #881265 Message #882273