CODEWITHSUNDEEP
c#sql-server
Michel
Michel

Reputation: 23615

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

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 :-)

Upvotes: 3

Views: 1393

Answers (1)

IronGeek
IronGeek

Reputation: 4883

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.

Upvotes: 6

Related Questions