Solved

how to write a recursive sql query in the following scenario

Posted on 2013-02-07
5
208 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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