Solved

how to write a recursive sql query in the following scenario

Posted on 2013-02-07
5
200 Views
Last Modified: 2013-04-30
I have a table called Relation containing  records of terms with four fields Relation(id, maintermid, relatedtermid, relationship). some examples of records in table

11892	1	9990	3
11893	2	9996	3
11894	3	17	3
11895	4	19	3
1	4	9995	4
11896	5	9996	3
11897	6	17	3
11898	7	10574	3
11899	8	9686	3
2	8	16	4
3	8	12	4
4	8	14	4
5	8	15	4
6	8	9876	4
7	8	9996	4
8	8	9910	4
9	8	9884	4
10	8	10	4
11900	9	9456	3
11901	10	8	3
11902	11	9456	3
11903	12	8	3
11904	13	9456	3
11905	14	8	3
11906	15	8	3
11907	16	8	3
11	16	9992	4
12	17	6	4
13	17	3	4
11908	17	20	3

Open in new window


3 means the related term is parent of the main term and 4 means related term is a child term

Each main term can have 1 or more related terms. By related terms i mean the term can have child, sub child and sub sub child or similarly parent, grand parent and grand grand parent etc. I need to write a query which will get a termid and return me all the terms in a tree i.e. every item or term in the tree will have parent id.
0
Comment
Question by:mmalik15
  • 3
  • 2
5 Comments
 
LVL 25

Expert Comment

by:SStory
Comment Utility
Where are the root level items?  It seems there would be root or top level items that would have no parents, but from looking at your example all have parents, so this seems to be never ending.
0
 

Author Comment

by:mmalik15
Comment Utility
this query gives us the top level terms

SELECT * FROM term 
	WHERE 	id NOT IN 
				(SELECT maintermid FROM
					(select top 100 percent maintermid, count(*) as NumOccurs from relation where relationship = 3 group by maintermid order by numoccurs ) s
				)

Open in new window


Also see attached an example of the tree structure i need to build
hireachy.png
0
 

Author Comment

by:mmalik15
Comment Utility
The columns of the Term table are id, type, name,dateadded. Relation table maintermid or relatedtermid is the id coming from term table.
0
 
LVL 25

Accepted Solution

by:
SStory earned 500 total points
Comment Utility
Is this a new database already set in stone or a new project? If a new project I would consider a different data structure.

The idea is to have all terms with a unique ID and a ParentID, which is NULL for root level objects.  Then have an INDEX table--call it TermsIndex if you like.

As people add terms to the database, do so through a stored procedure,
usp_AddTerm(Term,ParentID)

or (Term,ParentTerm) if you prefer.

Inside that stored procedure, you would add and item, say Anatomy.  Then when you added Cell Biology, with parent term Anatomy, inside the stored procedure it would query TermIndex where TermID= (the parent term id)...so 1.  It would insert a new record for each of those results, except having a TermID of the term being added.
so in the example, adding term 4, Teratology, would insert what is shown.
In reality there should be this also
TermID           RelatedID
    3                         1

It would also add a TermIndex record for the current term having the relatedId by the parentid.

So you would end up with an indexed list where you could issue a query...

Select * from Index where TermID=4
Order by RelatedID

or whatever.

With that you'd have the entire list for any given term.  This would be the easiest way I can think of doing it.
termtables.JPG
0
 

Author Comment

by:mmalik15
Comment Utility
thanks for the comment. Your approach seems good but its an existing database with thousands of records, stored procedures and views etc so i won't be able to change it
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now