Solved

how to write a recursive sql query in the following scenario

Posted on 2013-02-07
5
209 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 38863922
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
ID: 38863938
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
ID: 38863949
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
ID: 38864037
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
ID: 38864071
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

635 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