SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 doubly linked list
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

daidaluus
Yak Posting Veteran

73 Posts

Posted - 11/18/2009 :  07:34:25  Show Profile  Reply with Quote
I have a table that is tracing a doubly linked list - like structure.

create table t1 (id int primary key, nextid int, previd int)

with this sample data
insert into t1
select 9, 18, 2 UNION ALL
select 18, 6, 9 UNION ALL
select 11, 2, 7 UNION ALL
select 1, 8, 6 UNION ALL
select 3, 7, 0 UNION ALL
select 6, 1, 18 UNION ALL
select 7, 11, 3 UNION ALL
select 8, 0, 1 UNION ALL
select 2, 9, 11

the first node has previd = 0 and the last node has nextid = 0.

i need a query to show the chain-nodes:

id nextid previd
----------- ----------- -----------
3 7 0
7 11 3
11 2 7
2 9 11
9 18 2
18 6 9
6 1 18
1 8 6
8 0 1

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 11/18/2009 :  08:06:38  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Definately on SQL 2000? if so that's a shame as you could have done this in 2005 and up

/*
I have a table that is tracing a doubly linked list - like structure.

create table t1 (id int primary key, nextid int, previd int)

with this sample data
insert into t1
select 9, 18, 2 UNION ALL
select 18, 6, 9 UNION ALL
select 11, 2, 7 UNION ALL
select 1, 8, 6 UNION ALL
select 3, 7, 0 UNION ALL
select 6, 1, 18 UNION ALL
select 7, 11, 3 UNION ALL
select 8, 0, 1 UNION ALL
select 2, 9, 11

the first node has previd = 0 and the last node has nextid = 0.

i need a query to show the chain-nodes:

id nextid previd
----------- ----------- -----------
3 7 0
7 11 3
11 2 7
2 9 11
9 18 2
18 6 9
6 1 18
1 8 6
8 0 1
*/

DECLARE @startId INT SET @startId = 3

DECLARE @table1 TABLE (
	[ID] INT PRIMARY KEY
	, [nextID] INT
	, [prevID] INT
	)
INSERT @table1
          SELECT 9, 18, 2
UNION ALL SELECT 18, 6, 9
UNION ALL SELECT 11, 2, 7
UNION ALL SELECT 1, 8, 6
UNION ALL SELECT 3, 7, 0
UNION ALL SELECT 6, 1, 18
UNION ALL SELECT 7, 11, 3
UNION ALL SELECT 8, 0, 1
UNION ALL SELECT 2, 9, 11


; WITH chain (
	[id]
	, [nextId]
	, [prevId]
	, [LEVEL]
	)
AS (
	-- Anchor
	SELECT
		[ID]
		, [nextID]
		, [prevID]
		, 0
	FROM
		@table1
	WHERE
		[ID] = @startID

	-- Recursive
	UNION ALL SELECT
		t.[ID]
		, t.[nextID]
		, t.[prevId]
		, c.[LEVEL] + 1
	FROM
		chain c
		JOIN @table1 t ON t.[ID] = c.[nextID]
	)
SELECT
	[Id]
	, [nextID]
	, [prevID]
FROM
	chain
ORDER BY
	[Level]



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 11/18/2009 :  08:07:03  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
You could do this for sql sever 2000

/*
I have a table that is tracing a doubly linked list - like structure.

create table t1 (id int primary key, nextid int, previd int)

with this sample data
insert into t1
select 9, 18, 2 UNION ALL
select 18, 6, 9 UNION ALL
select 11, 2, 7 UNION ALL
select 1, 8, 6 UNION ALL
select 3, 7, 0 UNION ALL
select 6, 1, 18 UNION ALL
select 7, 11, 3 UNION ALL
select 8, 0, 1 UNION ALL
select 2, 9, 11

the first node has previd = 0 and the last node has nextid = 0.

i need a query to show the chain-nodes:

id nextid previd
----------- ----------- -----------
3 7 0
7 11 3
11 2 7
2 9 11
9 18 2
18 6 9
6 1 18
1 8 6
8 0 1
*/

DECLARE @startId INT SET @startId = 3

DECLARE @table1 TABLE (
	[ID] INT PRIMARY KEY
	, [nextID] INT
	, [prevID] INT
	)
INSERT @table1
          SELECT 9, 18, 2
UNION ALL SELECT 18, 6, 9
UNION ALL SELECT 11, 2, 7
UNION ALL SELECT 1, 8, 6
UNION ALL SELECT 3, 7, 0
UNION ALL SELECT 6, 1, 18
UNION ALL SELECT 7, 11, 3
UNION ALL SELECT 8, 0, 1
UNION ALL SELECT 2, 9, 11


-- 2000 Version
DECLARE @ins INT
DECLARE @level INT

SET @level = 0

DECLARE @newTable TABLE (
	[id] INT PRIMARY KEY
	, [nextId] INT
	, [prevId] INT
	, [LEVEL] INT
	)

INSERT INTO @newTable ([Id], [nextId], [prevId], [LEVEL])
SELECT [Id], [nextId], [prevID], @level FROM @table1 WHERE [ID] = @startID
SET @ins = @@ROWCOUNT

WHILE @ins > 0 BEGIN
	INSERT INTO @newTable ([Id], [nextId], [prevId], [LEVEL])
	SELECT 
		t.[ID]
		, t.[nextID]
		, t.[prevId]
		, c.[LEVEL] + 1
	FROM
		@newTable c
		JOIN @table1 t ON t.[ID] = c.[nextID]
	WHERE
		c.[level] = @level

	SET @ins = @@ROWCOUNT
	SET @level = @level + 1
END

SELECT * FROM @newTable ORDER BY [Level]



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 11/18/2009 :  08:08:05  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Either way -- you'll run into a problem if you've got any circular reference.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

daidaluus
Yak Posting Veteran

73 Posts

Posted - 11/18/2009 :  08:55:42  Show Profile  Reply with Quote
Thank for your replies. there is no circular reference. is there a way to do it in sql server 2000 without loops and cursor-like operations?
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 11/18/2009 :  10:50:35  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
I think that the while loop I posted is probably the best solution on 2000. it is a set based loop rather than a row by bloody row loop -- if you put multiple rows into the initial table it will operate on all of them at once.

You could do the same thing with a recursive function call but I think you will be limited to 31 levels with that.

performance hit too much with the while loop?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

daidaluus
Yak Posting Veteran

73 Posts

Posted - 11/19/2009 :  00:37:19  Show Profile  Reply with Quote
I'll try your solution. Thank you guys

Edited by - daidaluus on 11/19/2009 00:37:57
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000