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:
Create new stored procedure for find above level from binary tree of current user