Get All Parent and Child Companies (plus child of child companies) up to Nth level
Get All Parent and Child Companies (plus child of child companies) up to Nth level using recursive CTE declare @companyId int set @companyId = 1 ;WITH cte AS ( SELECT CompanyId, CompanyName, ParentCompanyId, 0 as steps FROM dbo.Company --WHERE ParentCompanyId IS NOT NULL WHERE companyid = @companyId UNION ALL SELECT c.CompanyId, c.CompanyName, c.ParentCompanyId, cte.steps +1 as steps FROM dbo.Company AS c inner JOIN cte ON cte.CompanyId = c.ParentCompanyId ) SELECT CompanyId, CompanyName, ParentCompanyId, steps FROM cte;