Loops possible within SQL queries? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Loops possible within SQL queries?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-27 : 12:04:01

Sudipa writes "Hi,

I wish to do a recursive join till a given criteria is achieved. How should I write the SQL?

For example:

I have a table which has a tree structure as

Parent Level 0
Child Level 1
Child Level 2
Child Level 3

I want to get the tree thru an SQL query. The Records in TreeTable are as below :


MemId      ParentId       Level    GroupId
------     --------       -----    -------
P1         0              1         1
C11        P1             2         1
C12        C11            3         1
C13        C12            4         1
P2         0              1         2
C21        P2             2         2
C22        C21            3         2

Now given MemId as C22 I should be able to get back the Hierarchy back to its parent i.e.


P2         0              1         2
C21        P2             2         2
C22        C21            3         2

Or given MemId as C13 I should be able to get back the Hierarchy back to its parent i.e.


P1         0              1         1
C11        P1             2         1
C12        C11            3         1
C13        C12            4         1

Please note that there is a common GroupId for a Hierarchy.
Let me know how I can achieve the above results thru a Single SQL query.I should be able to run this Query in the Query Analyzer for SQL Server 2005.
I was thinking of doing a recursive join till ParentId = '0' for a given GroupId.Don't know the syntax.

Please reply ASAP.It's urgent.

Regards,
Sudipa"

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2006-02-27 : 17:31:30

Here's a couple of links that uses recursion in a function that may be able to help you:

http://www.sql-server-helper.com/functions/get-tree-path.aspx
http://www.sql-server-helper.com/functions/get-tree-node-level.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-28 : 01:45:08

Also refer
http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources