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.