Solved

how to write a recursive sql query in the following scenario

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

929 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

13 Experts available now in Live!

Get 1:1 Help Now