CODEWITHSUNDEEP
sql-serverhierarchy
complez
complez

Reputation: 8322

SQL Server get parent list

I have a table like this:

id name parent_id
1  ab1  3
2  ab2  5
3  ab3  2
4  ab4  null
5  ab5  null
6  ab6  null

I need to do a query with input id = 1 (for an example) and results will be like this:

id name parent_id
5  ab5  null
2  ab2  5
3  ab3  2
1  ab1  3

(List all parents at all level start at item id = 1)

Upvotes: 2

Views: 4098

Answers (2)

Paul Creasey
Paul Creasey

Reputation: 28824

Something like this perhaps?

    WITH parents(id,name,parent,level)
    AS
    (
    SELECT
       ID,
       NAME,
       PARENT,
       0 as level
    FROM
       TABLE
    WHERE ID = 1
    UNION ALL
    SELECT
       ID,
       NAME,
       PARENT,
       Level + 1
    FROM 
       TABLE
    WHERE
       id = (SELECT TOP 1 parent FROM parents order by level desc)
    )
    SELECT * FROM parents

Upvotes: 5

Alex Brasetvik
Alex Brasetvik

Reputation: 11744

Use WITH RECURSIVE. Documentation and adaptable example: Recursive Queries Using Common Table Expressions.

Upvotes: 1

Related Questions