asked on

Document listing with child elements

I am currently making a knowledge base type document store, the problem I am encountering is that I want to associate documents to one another and also display them according to their association. Currently, I am storing the document data in one table and the associations in another. So :

tblDocument
DocumentiD
DocumentTitle

tblDocumentLinkiD
DocumentLinkiD
DocumentiD
DocumentParentiD
DocumentParentLevel2iD
DocumentParentLevel3iD

And my sql to pull back the associations :
see code snipet.

This works currently but if I want to have any more than 3 levels of association (Parent, Child1, Child of Child1, Child of Child 2) I have to add another column to my DocumentParen table which is inflexible really. Also, I am not sure how well it handles a document that is associated with more than one document. Do I need to look at a cursor with a recursive loop?

Sorry for the poor question title, I was unable to think of a good title!

Select DocumentiD, DocumentTitle 
FROM         
	tblDocument LEFT OUTER JOIN
	tblDocumentLinkiD ON tblDocument.DocumentiD = tblDocumentLinkiD.DocumentiD
Order By
	DocumentParentiD, DocumentParenitLevel2iD, DocumentParentLevel3iD, DocumentParentLevel4iD

Open in new window

Binuth

try this

Select Doc.DocumentiD, Doc.DocumentTitle 
FROM         
	tblDocument AS Doc
LEFT OUTER JOIN
  (
	SELECT DocumentiD AS DocumentiD  FROM tblDocumentLinkiD
	UNION 
	SELECT DocumentParentiD AS DocumentiD FROM tblDocumentLinkiD
	UNION 
	SELECT DocumentParentLevel2iD AS DocumentiD  FROM tblDocumentLinkiD
	UNION 
	SELECT DocumentParentLevel3iD AS DocumentiD  FROM tblDocumentLinkiD
  ) AS DocList
ON Doc.DocumentiD = DocList.DocumentiD

Open in new window

AbeSpain

ASKER

I solved this by creating a user defined function in the end that calls itself. The base table just had a reference to a parentid and function just pull back the parents until it reached the end.

CREATE FUNCTION [GetElementChilds] ( @KnowledgeBaseiD INT, @KnowledgeBaseTypeiD Int) 
RETURNS @result TABLE (
[ID] INT,
KnowledgeBaseTitle CHAR(50),
KnowledgeBaseParentiD INT,
ElementLevel Int)
 
AS 
 
BEGIN 
 
DECLARE @r INT 
DECLARE @i INT 
DECLARE @id INT 
DECLARE @tbl_temp TABLE( 
i INT identity, 
KnowledgeBaseiD INT , 
KnowledgeBaseTitle CHAR ( 50 ), 
KnowledgeBaseParentiD INT, 
ElementLevel Int) 
 
SET @i = 1 
 
INSERT INTO @tbl_temp 
SELECT KnowledgeBaseiD, KnowledgeBaseTitle, KnowledgeBaseParentiD, 0 
FROM tblKnowledgeBase 
	Where KnowledgeBaseTypeiD = @KnowledgeBaseTypeiD
 
SET @r = @@ROWCOUNT 
 
WHILE ( @i < = @r ) 
	BEGIN 
	  SELECT @id = KnowledgeBaseiD FROM @tbl_temp WHERE i = @i 
 
	  INSERT INTO @result 
		  SELECT KnowledgeBaseiD, KnowledgeBaseTitle, KnowledgeBaseParentiD,@@nestLevel
		FROM @tbl_temp 
		WHERE KnowledgeBaseiD = @id AND KnowledgeBaseParentiD = @KnowledgeBaseiD
 
	  IF @@ROWCOUNT > 0 
		BEGIN 
 
		   INSERT INTO @result 
		SELECT * FROM GetElementChilds ( @id, @KnowledgeBaseTypeiD) 
		END
 
	  SET @i = @i + 1 
	END
RETURN
END

Open in new window

AbeSpain

ASKER

This question can be closed now, accepting the only post would be wrong though, well, I haven't tried it to be honest.

ASKER CERTIFIED SOLUTION

Computer101

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial