Get all subitems (children, grandchildren) and parents from a self referencing table

Question

I want to create a 'selectable method' (doesn't matter if it is a view or a function or a stored procedure or something else, I don't know if there are other methods) to get all the direct parents and all children, grandchildren, grand-grandchildren etc. records from a self referencing table. The 'selectable method' I want to call from C# (.NET)

We have a table called entities, and it has (amongst others) a property parentId which refers to the Id field of the entities table. In this way we model the tree of entities (in our case elements of a building, think house, floor, room etc.)

The parent nodes, if visualized, are the ones which form a direct line to the root element.

The child...nodes can, if visualised, 'spread out'.

It's ok if the selection has to be done in two seperate statements.

In C# I know how to do this, but I don't want to fire lots of requests to the database and I think Sql Server can do this fast(er) by itself, but I don't know how :-)

You're looking for a recursive CTE - http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example/14275097#14275097 — MarkD, Jul 04 '14 at 08:09

IronGeek · Accepted Answer · 2014-07-04T10:09:49.140

Try using the following recursive CTE as a basis for your solution:

WITH TREE AS (
  SELECT     ROOT.Id        
            ,ROOT.ParentId  
            ,ROOT.Prop1
            ,ROOT.Prop2 
            ...
            ,ROOT.PropN                 
            ,1 AS Level 
  FROM       ENTITIES ROOT      
  -- list entities starting from ROOT node (no ParentId)
  WHERE      ROOT.ParentId IS NULL 
  -- or list entities starting from a specified node
  -- WHERE ROOT.Id = @entityId

  UNION ALL

  SELECT     CHILD.Id
            ,CHILD.ParentId 
            ,CHILD.Prop1
            ,CHILD.Prop2    
            ...
            ,CHILD.PropN                
            ,PARENT.Level + 1
  FROM       ENTITIES CHILD INNER JOIN 
             TREE PARENT ON CHILD.ParentId = PARENT.Id
  WHERE      CHILD.ParentId IS NOT NULL
)

SELECT *  FROM TREE

You could place the CTE inside a VIEW or a Stored Procedure if you need to pass parameters.

Get all subitems (children, grandchildren) and parents from a self referencing table

1 Answers1