Complicated recursive SQL Query

Posted on 2009-02-20
Last Modified: 2012-05-06
Please see question:

This is now for SQL Server 2005 NOT SQL CE.


Question by:rowansmith
    1 Comment
    LVL 25

    Accepted Solution

    Try -
    DECLARE @noName nvarchar(100)
    SELECT @noName = 'Group4'
    ;WITH CTE (noPK, noType, ngmPKChild)
    	--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
    	--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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    761 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

    10 Experts available now in Live!

    Get 1:1 Help Now