CODEWITHSUNDEEP
sql-servert-sql
Muhammad Jawad
Muhammad Jawad

Reputation: 122

Get All records with parent Child relationship to the Nth Level

I have a Company table having ParentCompnayID column in it along with other columns which tells which company is parent of other companies.

DECLARE @company TABLE
(
  CompanyID INT IDENTITY(1, 1) ,
  CompanyName VARCHAR(50) ,
  ParentCompnayID INT
)
INSERT  INTO @company
    ( CompanyName ,
      ParentCompnayID
    )
    SELECT  'Company A' ,
            0
    UNION
    SELECT  'Company AB' ,
            1
    UNION
    SELECT  'Company AA' ,
            1
    UNION
    SELECT  'Company AAA' ,
            2
SELECT  * FROM    @company

Now As you see in the code above Compnay A have parentCompanyID=0 which means it does not have any parent, where as Company AB and Company AA have parent which is Company A. Furthermore Company AAA have parent which is Compnay AA. Now my problem is that if i pass compnayID 1 in my query/procedure i want to get all its child companies plus the childs companies of Child of Company 1 and up to so on... According to the above code if I will pass CompanyID 1, I should get Company AA, Company AB and Company AAA (which is the child of Company AA)

Can anyone help me in building its sql. i am using SQL server 2012.

Upvotes: 0

Views: 1697

Answers (1)

Adil
Adil

Reputation: 148160

You can do this with the help of common table expression

declare @companyId int
set @companyId = 1
;WITH cte
AS
(
    SELECT CompanyId, CompanyName, ParentCompanyId, 0 as steps
    FROM dbo.tblCompany
    --WHERE ParentCompanyId IS NOT NULL
     WHERE companyid = @companyId
  UNION ALL
    SELECT  c.CompanyId, c.CompanyName, c.ParentCompanyId, cte.steps +1 as steps
    FROM dbo.tblCompany AS c
    inner JOIN cte ON cte.CompanyId = c.ParentCompanyId
)
SELECT CompanyId, CompanyName, ParentCompanyId, steps
FROM cte;

In the above query step is the level in the hierarchy.

Upvotes: 1

Related Questions