Hi,

I have a table as follows:

ID Description Parent
1 A 0
2 B 0
3 C 0
4 D 1
5 E 1
6 F 1
7 G 4
8 H 7
9 I 2
10 J 2
11 K 10

I'd like some pointers for a query that would return all parent rows.
For example if I search for ID=8 I should get the rows

ID
8
7
4
1

Thanks.

Hi, try not to use reserved words for table names and columns - ID,
Description, Name etc.

You will need to use something like a while loop.

DECLARE @Parent int
CREATE TABLE #TempTable (TempID int, TempDesc char, Parent int)
SET @Parent = 8

WHILE @Parent > 0
BEGIN
INSERT INTO #TempTable
SELECT * FROM Test
WHERE AID = @Parent
SET @Parent = (SELECT AParent FROM Test WHERE AID = @Parent)
END
SELECT * FROM #TempTable
DROP TABLE #TempTable

Hope this helps

Jay Freeman

[quoted text, click to view]