Query to get Linked List kind of data from the Table

hi Experts,

I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of

ID

Duplicate_of

State

77637

65702

Duplicate

65702

42217

Duplicate

42217

-

Verified

i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.

Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate

i appreciate if somebody can help me think in some line to give me some idea.

/soni

Assuming you are using 2005 you can use a common table expression (CTE) and use the following syntax:

DECLARE @.ID int

SET @.ID = 77637

;WITH Dupes (ID, Duplicate_of, State) AS

(

SELECT

ID, Duplicate_of, State

FROM

dbo.test

WHERE

ID = @.ID

UNION ALL

SELECT

T.ID, T.Duplicate_of, T.State

FROM

dbo.test T

INNER JOIN Dupes D ON D.Duplicate_of = T.ID

)

SELECT *

FROM Dupes

|||

hi Weaver,

Many Thanks for looking at my problem.

i forgot to mention i use SQL Server 2000 as of now. cannot upgrade to SQL Server 2005. :(

/Soni

|||

You can use this function to return the root of the tree (assuming you wanted to do this one at a time :)

set nocount on
drop table issue
drop function issue$getRoot
go
create table issue
(
issueId int primary key,
duplicateOf int references issue(issueId)
)
insert into issue
select 1, NULL
insert into issue
select 2, 1
insert into issue
select 3, 2
insert into issue
select 4, NULL
insert into issue
select 5, 4
go
create function issue$getRoot
(
@.issueId int
)
returns int
as
begin
while(1=1)
begin
select @.issueId = issue.issueId
from issue
join issue as dup
on dup.duplicateOf = issue.issueId
where dup.issueId = @.issueId

if @.@.rowcount = 0
break
end
return @.issueId
end
go

select dbo.issue$getRoot (5)

|||

hi Louis

Thanks a million for yr expert logic. its working!!!. :)

/Soni