how to write a recursive sql query in the following scenario

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.
mmalik15Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SStoryConnect With a Mentor Commented:
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
 
SStoryCommented:
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
 
mmalik15Author Commented:
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
 
mmalik15Author Commented:
The columns of the Term table are id, type, name,dateadded. Relation table maintermid or relatedtermid is the id coming from term table.
0
 
mmalik15Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.