How to find all the descendants with SQL?

Question

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

Do you mean you wish to find the direct first-generation descendants of each entity, or the entire tree of _n_ generations beneath an entity? — Michael Berkowski, Sep 30 '11 at 18:33

score 2 · Accepted Answer · answered Sep 30 '11 at 18:38

2

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;

answered Sep 30 '11 at 18:38

Joe Stefanelli

132,803
19
237
235

Wow this is neat, thanks. But I don't understand the @GivenEntity part, what does it mean? Sorry, I'm new to this – HappyDeveloper Sep 30 '11 at 20:01
@HappyDeveloper In your question, you said "I need to find all the descendants of a given entity", so `@GivenEntity` is the variable representing the value for the entity whose descendants you wish to find. – Joe Stefanelli Sep 30 '11 at 20:44

score 1 · Answer 2 · edited May 23 '17 at 12:19

1

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.

edited May 23 '17 at 12:19

Community

1
1

answered Sep 30 '11 at 18:30

webbiedave

48,414
8
88
101

I'm not using the ORM, only the DBAL. Can I still use this? I'll read it anyway, thanks – HappyDeveloper Sep 30 '11 at 18:38
You can run a single SQL statement, if the SQL statement is recursive (like a CTE) or loops. – Bill Gregg Jan 29 '15 at 20:24

How to find all the descendants with SQL?

2 Answers2