Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Complicated recursive SQL Query

Posted on 2009-02-20
1
Medium Priority
?
252 Views
Last Modified: 2012-05-06
Please see question: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_24144149.html

This is now for SQL Server 2005 NOT SQL CE.

Thanks.

-Rowan
0
Comment
Question by:rowansmith
1 Comment
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 23691460
Try -
DECLARE @noName nvarchar(100)
 
SELECT @noName = 'Group4'
 
;WITH CTE (noPK, noType, ngmPKChild)
AS
(
	--Anchor member
	SELECT o.noPK, o.noType, g.ngmPKChild
	FROM netobject o
	INNER JOIN netgroupobject g on g.ngmPKParent = o.noPK
	WHERE o.noName = @noName
 
	UNION ALL
 
	--Recursive member
	SELECT o.noPK, o.noType, g.ngmPKChild
	FROM CTE c
	INNER JOIN netobject o on o.noPK = c.ngmPKChild
	INNER JOIN netgroupobject g on g.ngmPKParent = c.ngmPKChild
	WHERE g.ngmPKChild IS NOT NULL
) 
SELECT distinct o.*
FROM  netobject o
INNER JOIN CTE c on c.ngmPKChild = o.noPK
WHERE o.noType = 'value'

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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.​
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 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