SQL Server recursive query, Which returns all ancestors of an item

by

We will create a recursive query when getting data as per parent and its level. Mostly we use this query in MLM software (Multilevel marketing software). Using this you can easy to get who is my parent till the level we wanted to find.

declare @CurrentUser int = 9;

;with CteMyParents as (
   select Id, ParentId, Username, 1 as [Level]
   from [User]
   where Id = @CurrentUser
   union all
   select c.Id, c.ParentId, c.Username, p.[Level] + 1
   from [User] c
     join CteMyParents p on p.ParentId = c.Id
)

select *
from CteMyParents

for more info follow this URL:

https://stackoverflow.com/questions/16749095/sql-recursive-query-that-gets-all-ancestors-of-an-item

Create new stored procedure for find above level from binary tree of current user

Post Views: 224

4 thoughts on “SQL Server recursive query, Which returns all ancestors of an item”

  1. I think there is something about mlm studion..I want to know more about mlm studion..I’m really interested in learning ..if there is an opportunity for me to understand about mlm studion, I am very proud .. thank you

    Reply

Leave a Comment