• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Complicated recursive SQL Query

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
rowansmith
Asked:
rowansmith
1 Solution
 
reb73Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now