0

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
values
('John',null)
,('Doe',1)
,('Mike',1)
,('Neil',3)
,('Sylvia',4)
,('Mary',2)

+----+--------+----------+
| 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. – Erik Darling Feb 7 '18 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. – SqlWorldWide Feb 7 '18 at 19:05
  • @sp_BlitzErik see edit – M B Feb 7 '18 at 19:15
  • 1
    Okay, cool, go ahead and give us your test data as inserts. – Erik Darling Feb 7 '18 at 19:29
  • 1
    Have a look at: OPTION (MAXRECURSION xxx). Try it by adding OPTION (MAXRECURSION 0) after you select statement. – McNets Feb 7 '18 at 20:13
0

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

CREATE TABLE #table (
    id INT identity(1, 1)
    ,[Name] VARCHAR(16)
    ,ParentID INT NULL
    )

INSERT INTO #table
VALUES('John',null)
,('Doe',1)
,('Mike',1)
,('Neil',3)
,('Sylvia',4)
,('Mary',2)

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

    UNION ALL

    SELECT r.ID
        ,t.parentID,0 DirectP
    FROM #table t
    INNER JOIN CTE r ON t.id = r.parentID
    WHERE t.ParentID IS NOT NULL
    )
SELECT r.ID
    ,r.ParentID
   ,DirectP
FROM CTE r

ORDER BY id
    ,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 Feb 8 '18 at 16:13
  • @RDFozz, Corrected.silly mistake – KumarHarsh Feb 8 '18 at 17:06
  • It doesn't return the desired result. dbfiddle.uk/… – McNets Feb 8 '18 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 Feb 9 '18 at 3:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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