How to get a tree of children for the parent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get a tree of children for the parent

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-05-16 : 16:04:35

I've this table Company with Columns and sample data like below:

CompanyID, CompanyName, ParentCompanyID, UserID....etc.
0, klm, -1, 1000
1, abc, 2, 1001
2, xyz, 0, 1002
3, pqr, 2, 1003
4, mno, 3, 1004

I'm trying to create a procedure that'll return all the children/subchildren for a parent. For example - if we pass 1002 - it should return Company 1,2,3 and 4 - because 2 is the parent of 1 and 3, and 3 is the parent of 4.

Need help please.
Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-05-16 : 17:00:24

Assuming you have SQL 2005 or later you can use a recursive common table expression (CTE) for this.
You can search here for plenty of examples (search for hierarchy or parent-child).
But here is a quick example of a recursive CTE based on your sample:


declare @t table (CompanyID int, CompanyName varchar(10), ParentCompanyID int, UserID int)
insert @t
select  0, 'klm', -1, 1000
union all select 1, 'abc', 2, 1001
union all select 2, 'xyz', 0, 1002
union all select 3, 'pqr', 2, 1003
union all select 4, 'mno', 3, 1004

;with rcte as 
(
       select companyid, companyname, parentCompanyID, Userid
       from @t
       where  userid = 1002
       union all
       select t.companyid, t.companyname, t.parentCompanyID, t.Userid
       from   rcte r
       join   @t t on t.parentCompanyID = r.companyid
)
select * from rcte

OUTPUT:
companyid   companyname parentCompanyID Userid
----------- ----------- --------------- -----------
2           xyz         0               1002
1           abc         2               1001
3           pqr         2               1003
4           mno         3               1004

Do you need help incorporating this into a stored procedure as well or are you good on that part?

Be One with the Optimizer
TG

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-05-16 : 17:14:03

Hi TG, I'll be able to incorporate it in the procedure.
Then I'll let you know if it's working.
Thanks so much.

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-05-16 : 19:13:55

It worked, thanks again TG.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources