We help IT Professionals succeed at work.

Document listing with child elements

AbeSpain
AbeSpain asked
on
172 Views
Last Modified: 2010-03-20
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

Comment
Watch Question

Commented:
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

Author

Commented:
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

Author

Commented:
This question can be closed now, accepting the only post would be wrong though, well, I haven't tried it to be honest.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.