SQL Recursive Query only return the last row

Question

I am trying to get the simple SQL Server 2008 Recursive Query to work.

Following these examples: http://msdn.microsoft.com/en-us/library/ms186243.aspx and SQL Server recursive query

I have a table, with id and parentID:

ID  fParent fName
2   NULL     root   
3    2       Drug_Error 
4    2       Incident   
5    4       2007   
6    4       2009   
7    5       2007-1
8    7       2008-2

with the following query

with recury as (
Select 
fs1.ID ,fs1.FParent,fs1.FName 
from  FoldersStructure as  fs1
where fs1.FParent =null
union all
select fs2.id,fs2.FParent,fs2.FName 
from FoldersStructure as  fs2
inner join recury as r on fs2.FParent= r.ID 
)
select ID,FParent,FName
from recury 
where ID=8

I was hoping to get:

2    null    root
4    2        incident
5    4        2007
7    5        2007-1
8    7        2007-2

But I only get the last one. thanks in advance.

Have you tried omitting your Where clause? It specifically says you will only get the last row. — Dave Johnson, Apr 29 '13 at 15:46
@DaveJohnson ,if i remove where ID=8 I get every row in the table. — Lambda, Apr 29 '13 at 16:00
The names of the columns being selected in the CTE do not match the names of the columns in your example table! — Ɖiamond ǤeezeƦ, Apr 29 '13 at 16:18

score 4 · Answer 1 · answered Apr 29 '13 at 15:49

with recury as (
Select 
fs1.ID ,fs1.FParent,fs1.FName 
from  FoldersStructure as  fs1
where fs1.ID=8

union all
select fs2.id,fs2.FParent,fs2.FName 
from FoldersStructure as  fs2
inner join recury as r on fs2.ID= r.FParent

)
select ID,FParent,FName
from recury 
order by ID

SQL-Fiddle

Ɖiamond ǤeezeƦ · Accepted Answer · 2013-04-29T16:15:44.057

Remove the WHERE clause from the statement because it is limiting the resultset to rows where Id = 8. Based on the first comment below, I now understand your requirement! To use 8 as your starting point and to retrieve all parent rows:

WITH recury (Id, ParentId, Name, Level) AS
(
  SELECT fs1.Id ,fs1.ParentId,fs1.Name, CONVERT(int, 0)
  FROM  FoldersStructure AS  fs1
  WHERE fs1.Id = 8
  UNION ALL
  SELECT fs2.Id,fs2.ParentId,fs2.Name, Level - 1
  FROM FoldersStructure AS  fs2
  JOIN recury AS r ON fs2.Id = r.ParentId
)
SELECT Id, ParentId, Name, Level
FROM recury 
ORDER BY  Level;

This code will work if the Ids of the parent rows are not in numeric order. If your parent rows always guaranteed to be in numeric order, you can omit the Level column introduced in the CTE and sort on the Id column instead as per bummi's answer.

SQL fiddle example: http://sqlfiddle.com/#!3/2af0c/4

but if I remove where Id = 8 how can it find the starting point? — Lambda, Apr 29 '13 at 16:03
Great that worked Too, many thanks, they are in numerical order But I will use your level to be safe. — Lambda, Apr 29 '13 at 16:37

SQL Recursive Query only return the last row

2 Answers2

Linked