January 31, 2006 at 9:06 am
I have a table as
ID int
LocationID varchar(10)
DeletedFlag bit
LinkID int
Whenever a record's DeletedFlag is set to True, a new record is created. The LinkID in the new record holds the ID of the deleted record.
Sample data
ID LocationID DeletedFlag LinkID
1 1-000-001 1 4
4 2-000-001 1 9
9 NULL 1 12
12 NULL 0 NULL
When ID 12 is selected, I need to return a non-Null LocationID by going up the LinkID chain. In this example, it should return 2-000-001.
Can this be done in a single query without using a loop ?
January 31, 2006 at 10:25 am
Somthing like this (did not test, just an idea):
select t1.LocationID
from MyTable t1 join MyTable t2 on t1.ID = t2.linkID
where t2.ID = MyRequestedID (12 in this case)
Regards,Yelena Varsha
January 31, 2006 at 11:00 am
Yelena,
Your query returns Null. I changed the query but still can go up 1 row only.
CREATE TABLE #Test (ID int, LocationID varchar(20), DeletedFlag bit, LinkID int)
INSERT INTO #Test VALUES (1,'1-000-001',1,4)
INSERT INTO #Test VALUES (4,'2-000-001',1,9)
INSERT INTO #Test VALUES (9,NULL,1,12)
INSERT INTO #Test VALUES (12,NULL,0,NULL)
SELECT * FROM #test
SELECT t2.ID, t2.LocationID
FROM #Test t1 INNER JOIN #Test t2 ON t1.ID = t2.LinkID
WHERE t1.ID = 12
DROP TABLE #Test
January 31, 2006 at 11:09 am
The problem with joining you might need to join more than once, or even more than twice. You need to create an alternate method.
For reference you should google "Trees in SQL" there is alot of information out there about your problem.
I have created 2 methods a stored procedure that is called recursively. Problem with recursion is it can only go 32 levels deep. So if you can guarantee the nesting will not exceed 32 than it will work fine. But if not you'll have to use the second method which queries the ID and loops thru until it finds all "Children". It utilizes a Temp table so there is a little performance. If your Trees are large, or the Link table is Huge, you should consider saving the Tree information in like a tree table. Once again you'll learn alot about it if you look up Trees in SQL.
Here's one of the first articles I found
http://www.sqlteam.com/item.asp?ItemID=8866
create table Link(
ID int,
LocationID varchar(10),
DeletedFlag bit,
LinkID int)
Insert into Link
select 1,'1-000-001',1,4 union
select 4,'2-000-001',1,9 union
select 9,NULL,1,12 union
select 12,NULL,0,NULL
GO
-- 2 Methods, create a stored procedure that recursively goes up the hierarchy
-- to find the first item that is not null
create proc recursion @ID int, @LocationID varchar(10) output
as
set nocount on
if exists (select * from Link where [ID] = @ID and LocationID is not NULL)
select @LocationID = LocationID from Link where [ID] = @ID
else
begin
select @ID = [ID] from Link where [LinkID] = @ID
exec recursion @ID = @ID, @LocationID = @LocationID OUTPUT
End
Return
GO
declare @LocationID varchar(10)
exec recursion @ID = 12, @LocationID = @LocationID output
select @LocationID
-- OR Create a temp table solution that loops thru and creates the hierarchy in
-- a temp table then uses the temp table to query
drop table #Link
Create table #Link(ID int, LinkID int)
Insert into #Link
select A.ID, B.ID
from Link A
join Link B on a.Id = b.LinkID
where A.ID = 12
While (select count(*)
from #Link a
join Link b on a.LinkID = b.LinkID
where not exists (select *
from #Link C
where (A.id = c.ID
and b.ID = c.LinkID))
) > 0
begin
insert into #Link
select distinct A.ID, b.ID
from #Link a
join Link b on a.LinkID = b.LinkID
where not exists (select *
from #Link C
where (A.id = c.ID
and b.ID = c.LinkID))
End
select *
from Link
where LinkID = (select max(a.[LinkID])
from #Link A
join link b on a.[LinkID] = B.[ID]
where a.ID = 12
and LocationID is not NULL)
January 31, 2006 at 12:52 pm
Did not realise that the record could be deleted several times. In this case it is easier probably to loop using the same algorithm joining the table to itself and having re-assigning @I the value from the LinkID untill DeletedFlag = 0 for the record.
Regards,Yelena Varsha
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply