CODEWITHSUNDEEP
phpsqlsql-serversql-server-2005
HappyDeveloper
HappyDeveloper

Reputation: 12805

How to find all the descendants with SQL?

Each entity can have one parent. I need to find all the descendants of a given entity.

Is it possible with just SQL?

I can only think in terms of a script, and it won't be as fast as sql alone.

I'm using PHP and MS SQL Server 2005, and doctrine 2 DBAL

Upvotes: 0

Views: 626

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

For SQL Server 2005+, you can use a recursive CTE.

WITH cteRecursion AS (
    SELECT PrimaryKey, 1 AS Level
        FROM YourTable
        WHERE PrimaryKey = @GivenEntity
    UNION ALL
    SELECT t.PrimaryKey, c.Level+1
        FROM YourTable t
            INNER JOIN cteRecursion c
                ON t.ParentKey = c.PrimaryKey
)
SELECT PrimaryKey, Level
    FROM cteRecursion
    ORDER BY Level, PrimaryKey;

Upvotes: 2

webbiedave
webbiedave

Reputation: 48887

PHP will run one SQL statement at a time so you will need to create this list in a loop. A good alternative is to use nested sets.

Upvotes: 1

Related Questions