FK on same table

Question

Hi all,

I have table with ID who is PK and I have in same table column ParentID who is in some cases ID's.(I say some cases because for example ParentID can be 0 but this ID not have in ID's column). I try to create Query:

ID     ParentID     Value
1      0            Test
2      0            Test01
3      2            Test02
4      3            Test03

And when I send ID=4 I need this result:

ID     ParentID     Value
2      0            Test01
3      2            Test02
4      3            Test03

Any help...

Answer 1

Don't use 0 (zero) but null to signal "no parent present".

Answer 2

try this:

Declare @Id Integer Set @Id = 4
With Child(Id, ParentId, Value, Level) As
  ( Select Id, ParentId, Value, 0 as Level
    From table 
    Where id = @Id
    Union All
    Select t.Id, t.ParentId, t.Value, level + 1
    From Child c Join table t 
       On t.Id = c.ParentId
  )
Select id. ParentId, Value, Level
From Child
Order By Id

Answer 3

This looks like a tree traversal problem to me, which doesn't lend itself particularly well to set-based operations. The following works, but it's not too elegant:

-- turn this into stored proc?
declare @inputId int
set @inputId = 4


declare @Ids table (ID int)
insert into @Ids values (@inputId)

declare @reccount int
set @reccount = 1
declare @lastreccount int
set @lastreccount = 0
while @reccount <> @lastreccount
begin
    set @lastreccount = @reccount
    insert into @Ids 
        select ParentID from recursiveTest 
            where ID in (select ID from @Ids)
                and ParentID not in (select ID from @Ids)
    set @reccount = (select COUNT(*) from @Ids)                      
end

select * from recursiveTest where ID in (select ID from @Ids);