I have a basic sql table with an ID and an optional parentID from the same table. This has multiple levels. I need to get a list of all IDs with each of its parents all the way to the top.

if object_id('tempdb..#table') is not null drop table #table
create table #table (id int identity(1,1), [Name] varchar(16), ParentID int null)
insert into #table

| id |  Name  | ParentID |
|  1 | John   | NULL     |
|  2 | Doe    | 1        |
|  3 | Mike   | 1        |
|  4 | Neil   | 3        |
|  5 | Sylvia | 4        |
|  6 | Mary   | 2        |

I need to get a list like this:

ID  | Parent | DirectParent
2     1       1
3     1       1
4     3       1
4     1       0
5     4       1
5     3       0
5     1       0
  • It ignores any record that does not have a parent.
  • For each child there is a record matching it to one of its parents
  • There is a bit column to know which parent is the one directly above it

How can I query the above table to get this data?

Edit: see what I have been working with

 WITH Recursives AS (
        SELECT   CAST(ID AS VARCHAR(MAX)) + '\' ID,*       
        FROM    table
        WHERE   id=@ID
        UNION ALL
        SELECT  r.ID + CAST(t.ID AS VARCHAR(MAX)) + '\', t.*
        FROM    table t 
        INNER JOIN Recursives r    ON  t.parentID= r.ID)

    SELECT ID,Name,Parent
    FROM Recursives r

I use this to get a list for a specific parent all the way down.

  • I can't figure out how to do this for the entire table without getting an error
    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
  • How can I get the same child multiple times. Once with each parent?
  • 2
    You'll wanna use a recursive cte. You should give writing the query a try, and post back if you have any questions. Commented Feb 7, 2018 at 18:58
  • 1
    Also mention the SQL Server version you are using. Some of the new tsql functions might not be available in older versions. Commented Feb 7, 2018 at 19:05
  • @sp_BlitzErik see edit
    – M B
    Commented Feb 7, 2018 at 19:15
  • 1
    Okay, cool, go ahead and give us your test data as inserts. Commented Feb 7, 2018 at 19:29
  • 1
    Have a look at: OPTION (MAXRECURSION xxx). Try it by adding OPTION (MAXRECURSION 0) after you select statement.
    – McNets
    Commented Feb 7, 2018 at 20:13

1 Answer 1


you just cannot do recursive on millions of rows.you have to put some filter

Try this,

F object_id('tempdb..#table') IS NOT NULL
    DROP TABLE #table

    id INT identity(1, 1)
    ,[Name] VARCHAR(16)
    ,ParentID INT NULL


AS (
    SELECT id
        ,ParentID,1 DirectP
    FROM #table


        ,t.parentID,0 DirectP
    FROM #table t
    INNER JOIN CTE r ON t.id = r.parentID

    ,ParentID DESC
  • Unless I'm completely mistaken, the only rows where DirectParent should be 1 are the rows taken from #table originally (in the first half of the CTE); you should be able to set 1 as DirectParent in the part before the UNION ALL, and 0 as DirectParent in the recursive part of the CTE, and avoid having to figure it out with the OUTER APPLY.
    – RDFozz
    Commented Feb 8, 2018 at 16:13
  • @RDFozz, Corrected.silly mistake
    – KumarHarsh
    Commented Feb 8, 2018 at 17:06
  • It doesn't return the desired result. dbfiddle.uk/…
    – McNets
    Commented Feb 8, 2018 at 18:17
  • @McNets , see user desire output and "tried query" is misleading,I followed "desire output".you can easily exclude id 1 from my query.Why id 6 is not in output is not clear to me,but i am sure it can be easily filtered if [MB] clear it out.So far whatever is clear ,output is very much correct.[MB] is also struggling with Recursive.
    – KumarHarsh
    Commented Feb 9, 2018 at 3:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.