self reference tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

self reference tables

Author

Topic

rakhigulati
Starting Member

1 Post

Posted - 2006-07-14 : 04:59:41

Hi ,

I have the following structure:

Item table
----------
ItemId
ItemName

Item Transaction Table
----------------------
TransactionId
GiverItemId
SenderItemId

the data is somewhat like this:

Item table
__________
1 abc
2 xyz
3 pnr
4 rew
5 dds
6 djs
7 dsf

Item Transaction table
---------------------
1 1 2
2 2 4
3 4 3
4 3 7

Now i have a reauirement to build a stored proc in which all the transactions starting from one transaction like, if i want to know the chain for item no 2 it shall give the following result:

2 4
4 3
3 7

if i want to know the chain for item no 3 then it shall give following
3 7

if i want to know the chain for item no 1 then it shall give following
1 2
2 4
4 3
3 7

Please help.. Its urgent.....

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-14 : 06:05:56

[code]declare @Item table
(
ItemId int,
ItemName varchar(10)
)

declare @ItemTrans table
(
TransactionId int,
GiverItemId int,
SenderItemId int
)

insert into @Item
select 1, 'abc' union all
select 2, 'xyz' union all
select 3, 'pnr' union all
select 4, 'rew' union all
select 5, 'dds' union all
select 6, 'djs' union all
select 7, 'dsf'

insert into @ItemTrans
select 1, 1, 2 union all
select 2, 2, 4 union all
select 3, 4, 3 union all
select 4, 3, 7

declare @itemid int

declare @result table
(
seq int identity(1,1),
GiverItemId int,
SenderItemId int
)

select @itemid = 1

while (@itemid is not null)
begin
print @itemid

insert into @result
select GiverItemId, SenderItemId
from @ItemTrans
where GiverItemId = @itemid

select @itemid = SenderItemId
from @ItemTrans
where GiverItemId = @itemid
if @@rowcount = 0 select @itemid = null
print @itemid

end

select GiverItemId, SenderItemId
from @result
order by seq[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources