How to do an upper recursive self-join in SQL Server?

Question

How can I do a recursive self-join in SQL Server ? I have a table like this:

TableID | ParentID
   1    |     NULL
   2    |        1
   3    |        1
   4    |        3
   5    |     NULL
   6    |        4
   7    |        6

I want to get the following results based on given TableID to get all the ParentsID related to the TableID, let's say I want to get all the parents for the TableID = 6 :

TableID
   6
   4
   3
   1

I'm stuck on this and i don't know how to get the result in SQL Query ... Hope to tell me the SQL Query to get the previous data

Use a recursive cte. http://msdn.microsoft.com/en-us/library/ms186243.aspx — Mark Byers, Oct 15 '11 at 21:54
Standard SQL has no support for recursive queries. There are exensions, such as oracle's "connect by prior" that do this, but they're extensions and non-standard. The usual method for this sort of thing is to just do a series of queries in a loop. — Marc B, Oct 15 '11 at 21:54
Perhaps `HierarchyID` data type would make the task easier, available since SQL server 2008. — ain, Oct 15 '11 at 22:12

xanatos · Answer 1 · 2011-10-16T07:56:50.320

It should be

; WITH MyQuery (TableID, ParentID, Level) AS
(
    SELECT M.TableID, M.ParentID, 0 AS Level 
        FROM MyTable M 
        WHERE M.TableID = 6 -- Here it's the row number where the query starts

    UNION ALL

    SELECT M.TableID, M.ParentID, Q.Level + 1 
        FROM MyTable M 
        INNER JOIN MyQuery Q ON M.TableID = Q.ParentID
)

SELECT * FROM MyQuery;

and as written by Byers, it's a Recursive Queries Using Common Table Expressions

The Level column is useless (it isn't "useless useless". It's useless for what you asked), I have added it because it's quite often inserted in these recursive queries. If you don't need it, delete it from the 3 places it appears.

It seems to be much more complex to do the Level in reverse (so that the grand-grand father is level 0, his childs are level 1...)

Note that this code will work with SQL Server >= 2005

How to do an upper recursive self-join in SQL Server?

1 Answers1

Linked