How to find all the descendants with SQL?

Each entity can have one parent. I need to find all the descendants of 开发者_Python百科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

---

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;

---

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.