Recurse the tree

Question

Recurse the tree

cwedgwood50

SSCommitted

Points: 1751
More actions
September 9, 2003 at 5:53 am

#159905

Hi,
I have the following 2 tables; The first stores documents as BLOB's the second allows a relationship between documents to be recorded. For example if document1 is a word mailmerge document and document2 is a spreadsheet with the mailmerge data in. A document can be dependent upon more than 1 other documents.
In the TBL_DocumentLink table the values of DocumentID and SubDocumentID are the values of the
TBL_Document.DocumentID of the master and sub-document.
What I need to be able to do is to create a stored proc where I can pass a documentID and return all documents that are dependent upon that document or one of it's dependent documents. I am at a bit of a loss how to do this recursive checking in SQL. does anyone know how this could be done?
Thanks In advance.
Cheers
Chris
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TBL_Document]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TBL_Document]
GO
CREATE TABLE [dbo].[TBL_Document] (
[DocumentID] [int] IDENTITY (1, 1) NOT NULL ,
[DocDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DocLongDesc] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DocTypeID] [int] NULL ,
[BIN] [image] NULL ,
[LoadPath] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TBL_DocumentLink]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TBL_DocumentLink]
GO
CREATE TABLE [dbo].[TBL_DocumentLink] (
[DocumentLinkID] [int] IDENTITY (1, 1) NOT NULL ,
[DocumentID] [int] NULL ,
[SubDocumentID] [int] NULL
) ON [PRIMARY]
GO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65111 More actions · Answer 1

Rough solution (not tested)

DECLARE @RecCount int

SET @RecCount = 1

CREATE TABLE #TempDoc (DocumentID int) 

CREATE TABLE #TempDoc1 (DocumentID int) 

CREATE TABLE #TempDoc2 (DocumentID int) 

INSERT INTO #TempDoc1 VALUES (@DocumentID) 

WHILE (@RecCount > 0)

BEGIN

DELETE FROM #TempDoc2 

INSERT INTO #TempDoc2 

SELECT d.SubDocumentID FROM #TempDoc1 t

INNER JOIN TBL_DocumentLink d

ON d.DocumentID = t.DocumentID

INSERT INTO #TempDoc SELECT DocumentID FROM #TempDoc2 

DELETE FROM #TempDoc1 

INSERT INTO #TempDoc1 SELECT DocumentID FROM #TempDoc2 

SELECT @RecCount = COUNT(*) FROM #TempDoc1 

END

SELECT d.DocumentID,d.DocDesc,d.DocLongDesc,d.DocTypeID,d.BIN,d.LoadPath 

FROM TBL_Document d 

INNER JOIN #TempDoc t ON t.DocumentID = d.DocumentID

DROP TABLE #TempDoc

DROP TABLE #TempDoc1

DROP TABLE #TempDoc2

Far away is close at hand in the images of elsewhere.
Anon.

cwedgwood50 SSCommitted Points: 1751 More actions · Answer 2

Thanks David,

This looks great! I'll give it a try.

Cheers

Chris