In SQL Server, how can I select all records in a recursive table?

Question

I have a recursive table in which each record has an ID and a PARENTID. PARENTID points to a different ID in the same table. Is there a way in SQL Server to select an entire "tree" in one statement? I could write a recursive function to jump from a parent to all the children, but I'd like a way to do it in one query.

In Oracle, it would look like this:

select
  id,
  parentid,
  nodename
from 
  MY_SCHEMA.MY_TABLE
  connect by nocycle prior parentid = id
start with id = :starting_id_number
order by
  id

What would the SQL Server equivalent be?

Answer 1

hi

id also had this problem but i didn't find a solution. so i created this workaround: i added a new column 'tree_id' so that my table looks like this.

treeid|id|parentid|nodename
1     |1 | null   | rootOfTreeOne
1     |2 | 1      | childOfRootOne
1     |3 | 1      | secondChild
2     |4 | null   | rootOfSecondTree
2     |5 | 4      | childofSecondTree

and created the tree in a program. the advantage is that you can get all nodes from the tree with one simple single select statement.

select * from tree_table where tree_id = 1;

but there also maybe some stored procedures that can help you solve your problem.

hope this helps..

Answer 2

I don't think you can get away with it without using recursion in your statement.

If you are able (or for future reference) you could try using modified preorder tree traversal, which will allow you to do this.

Explaining modified preorder tree traversal in this answer is out of the scope because it takes some explaining and playing to get to grips with.

I will point out that there is an overhead on MPTT for updating and inserting records, but selecting is generally significantly more efficient. In general, selects happen a whole lot more than updates/inserts so it's worth it, but it's worth having a think about your particular situation before diving straight in.

The article I've linked to I find an excellent explanation of MPTT.

Answer 3

You should be able to use Common Table Expressions to do a recursive query. Do a google search on "Recursive Queries Using Common Table Expressions", there is a msdn article.

sigh new users aren't allowed to add hyperlinks.

Answer 4

Here is an example I have thrown together for you. It demonstrates using a recursive common table expression (CTE).

CREATE TABLE #tempTable
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    parentID INT NULL
)

INSERT INTO #tempTable (parentID) VALUES (null)
INSERT INTO #tempTable (parentID) VALUES (1)
INSERT INTO #tempTable (parentID) VALUES (1)
INSERT INTO #tempTable (parentID) VALUES (2)
INSERT INTO #tempTable (parentID) VALUES (3)
INSERT INTO #tempTable (parentID) VALUES (2)
INSERT INTO #tempTable (parentID) VALUES (5)


SELECT * FROM #tempTable;

WITH RecursiveTable (ID, ParentID, Level)
AS
(
    --Anchor
    SELECT tt.ID, 
      tt.ParentID, 
      0 AS Level
    FROM #tempTable AS tt
    WHERE parentID IS null
    UNION ALL
    --Recursive member definition
    SELECT  tt.ID, 
      tt.ParentID, 
      LEVEL + 1
    FROM #tempTable AS tt
     INNER JOIN RecursiveTable rt ON
     tt.ParentID = rt.ID
)
SELECT * 
FROM RecursiveTable

DROP TABLE #tempTable

Edit: As an additional thought, in SQL Server 2008 there is a data type called hierarchyid that can be used to implement hierarchical data structures. See the following tutorial

http://technet.microsoft.com/en-us/library/bb677213.aspx

Answer 5

You can use CTE's like this;

CREATE TABLE TestTable
( 
    ID int primary key NOT NULL,
    ParentID int
)

INSERT INTO TestTable VALUES (0, null)
INSERT INTO TestTable VALUES (1, 0)
INSERT INTO TestTable VALUES (2, 0)
INSERT INTO TestTable VALUES (3, 1)
INSERT INTO TestTable VALUES (4, 3)


-- Get branch
;WITH TreeRecCTE (ID, ParentID, IDPath)
AS
(
   SELECT ID, ParentID, CONVERT(varchar(max), ID) As IDPath
      FROM TestTable
      WHERE ParentID IS NULL
   UNION ALL
   SELECT
      Child.ID,
      Child.ParentID,
      Parent.IDPath + '.' + CONVERT(varchar(100),Child.ID) As IDPath
     FROM TestTable As Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
  )
SELECT * FROM TreeRecCTE WHERE IDPath LIKE '%.1.%' ORDER BY ParentID ASC 


-- Get complete tree:
;WITH TreeRecCTE (ID, ParentID, IDPath)
AS
(
   SELECT ID, ParentID, CONVERT(varchar(max), ID) As IDPath
      FROM TestTable
      WHERE ParentID IS NULL
   UNION ALL
   SELECT
      Child.ID,
      Child.ParentID,
      Parent.IDPath + '.' + CONVERT(varchar(100),Child.ID) As IDPath
     FROM TestTable As Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
  )
SELECT * FROM TreeRecCTE ORDER BY ParentID ASC

Answer 6

See also this other tree navigation in SQL Server question