2

This is for SQL Server 2008 R2, I'm a novice at SQL so please be as specific as you can.

Table1 has some recursive structure built into it, where the ParentId is either Null meaning it's the root, or ParentId is the Id of another row in Table1 which denotes it as a child.

Example data set:

Table1Id    ParentId
--------------------------------------------
1           NULL
2           1
3           1
4           2
5           NULL
6           2
7           6
8           NULL
9           8

With the above example the table then has the following tree structure with 3 root nodes:

Root                    1                 5                 8
Child(teir1)        2       3                               9
Child(teir2)      4   6
Child(tier3)          7
....

Is there a way to return only the Root row given any of the row Ids? For example:

InputId      ReturnedRowId
----------------------------
1             1
2             1
3             1
4             1
5             5
6             1
7             1
8             8
9             8

Any help would be appreciated.

JaySym
  • 63
  • 1
  • 8
  • 1
    You can do a recursive self-join using SQL: http://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join-in-sql-server – Dave Feb 16 '16 at 22:20
  • Could you please specify RDBMS you are using? The answer will very much depend on it. – Paul Feb 16 '16 at 22:45
  • This is for SQL Server 2008 R2 – JaySym Feb 16 '16 at 22:52
  • @Dave Thank you for the reply but I'm not sure how to use that one. When I try the suggested answer on table 2 with a specific Id, I still get back a list of rows, not just the parent row. – JaySym Feb 16 '16 at 22:59
  • Use a common table expression: https://technet.microsoft.com/en-us/library/ms175972%28v=sql.105%29.aspx –  Feb 17 '16 at 07:38

4 Answers4

2

You can use a CTE and traverse the hierarchy

    IF OBJECT_ID('tempdb..#testData') IS NOT NULL
        DROP TABLE #testData
    CREATE TABLE #testData (  
         Table1Id           INT
        ,ParentId           INT NULL
    )

    INSERT INTO #testData ( Table1Id, ParentId )
    VALUES
         (1, NULL )
        ,(2, 1 )
        ,(3, 1 )
        ,(4, 2 )
        ,(5, NULL )
        ,(6, 2 )
        ,(7, 6 )
        ,(8, NULL )
        ,(9, 8 )


    DECLARE @InputId INT

    SET @InputId = 2  --<<--Change this as appropriate

    ;WITH cteTraverse
    AS
    (
        SELECT
            T.Table1Id, T.ParentId
        FROM
            #testData T
        WHERE
            Table1Id = @InputId
        UNION ALL
        SELECT
            T1.Table1Id, T1.ParentId
        FROM
            #testData T1
        INNER JOIN
            cteTraverse T2 ON T1.Table1Id = T2.ParentId 
    )
    SELECT
        @InputId '@InputId', Table1Id 'ReturnedRowId'
    FROM
        cteTraverse
    WHERE
        ParentId IS NULL
Mazhar
  • 3,797
  • 1
  • 12
  • 29
2

This query do the job.

with CTE as 
(
    Select Table1ID as ID, Table1ID as Ancestor, 0 as level
    from Table1

    UNION ALL

    Select ID, ParentID, level + 1
    from Table1
        inner join CTE on CTE.Ancestor = Table1.Table1ID
    where ParentID is not NULL
)
,
R_only as 
(
Select ID as ID, MAX(level) as max_level 
from CTE 
group by ID
)

select CTE.ID, Ancestor 
from CTE inner join R_only on CTE.ID = R_only.ID and CTE.level = R_only.max_level
order by CTE.ID
Polux2
  • 552
  • 3
  • 12
  • Ah I see the logic you are using here is the same as TT.'s Answer above, just a few typos here and there. Very cool thank you. – JaySym Feb 17 '16 at 20:38
1

Here's a script that finds the root nodes for the nodes table you have. What it does:

  • In the first CTE, recurses the nodes until the parent is found. Recursion keeps track of the depth in column level.
  • In a second CTE, determines the maximum depth for each node: max_level. This is the depth where the parent was determined.
  • Select the nodes with maximum depth.
CREATE TABLE #tree(table1_id INT PRIMARY KEY,parent_id INT);
INSERT INTO #tree(table1_id,parent_id)VALUES
    (1,NULL),(2,1),(3,1),(4,2),(5,NULL),(6,2),(7,6),(8,NULL),(9,8);

;WITH cte_tr AS (
    SELECT table1_id, parent_id, level=0
    FROM #tree
    UNION ALL
    SELECT t_c.table1_id, t_p.parent_id, level=t_c.level+1
    FROM cte_tr AS t_c
         INNER JOIN #tree AS t_p ON
             t_p.table1_id=t_c.parent_id
    WHERE t_p.parent_id IS NOT NULL
),
cte_ml AS (
    SELECT table1_id, max_level=MAX(level)
    FROM cte_tr
    GROUP BY table1_id
)
SELECT cte_tr.table1_id, root_node=ISNULL(cte_tr.parent_id,cte_tr.table1_id)
FROM cte_tr 
     INNER JOIN cte_ml ON 
         cte_ml.table1_id=cte_tr.table1_id AND 
         cte_ml.max_level=cte_tr.level
ORDER BY cte_tr.table1_id

DROP TABLE #tree;

Result:

+-----------+-----------+
| table1_id | root_node |
+-----------+-----------+
|         1 |         1 |
|         2 |         1 |
|         3 |         1 |
|         4 |         1 |
|         5 |         5 |
|         6 |         1 |
|         7 |         1 |
|         8 |         8 |
|         9 |         8 |
+-----------+-----------+
TT.
  • 15,774
  • 6
  • 47
  • 88
  • This is very detailed, and thank you for your explanation above as well, very informative. Thank you very much. – JaySym Feb 17 '16 at 20:40
  • @JaySym You're welcome... as usual I see an expected result and type out an answer fast. I missed the fact that you wanted it for a specific ID ((that's what you get when you balance a job, a private life and browsing SO ;-)). I see there is already an answer that does it for a specific node ID only. In any case it doesn't hurt to dissect this answer (or any other answer that works really) to see how it works. For instance, how would you go about this when instead of finding the root for one node, you wanted to find the roots for an input table? GL! – TT. Feb 17 '16 at 21:01
0

Use start with and connect by prior. Check this documentation here

http://psoug.org/reference/connectby.html

I am not sure if "connect by" is available in other databases other than Oracle. Check it out.

You could also try with clause. Someone has tried here.

Simulation of CONNECT BY PRIOR of ORACLE in SQL SERVER

With works somewhat like this

with query1 as (select .... from .... where ....),
     query2 as (select .... from .... where ....)
select ...
  from query1 q1,
       query2 q2
 where q1.xxxxx = q2.xxxx
Community
  • 1
  • 1
Shriram M.
  • 383
  • 1
  • 7