SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Getting a tree using parent-child
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlbug
Posting Yak Master

Canada
197 Posts

Posted - 09/27/2013 :  18:17:00  Show Profile  Reply with Quote
Hi,

Here is the table - Company with fields:
CompanyID, ParentCompanyID (both integers)

Given a CompanyID - I want to get all the children for the Company.

I did similar procedures but somehow, could not get this to work.
Thanks so much.

sqlbug

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/28/2013 :  03:13:41  Show Profile  Reply with Quote
use CTE


;With Company_Tree
AS
(
SELECT CompanyID,ParentCompanyID,CAST(0 AS int) AS Level
FROM Company
WHERE ParentCompanyID IS NULL
UNION ALL
SELECT c.CompanyID,c.CompanyParentID,ct.Level + 1
FROM Company c
INNER JOIN Company_Tree ct
ON ct.CompanyID = c.ParentCompanyID
)

SELECT *
FROM Company_Tree
ORDER BY Level

OPTION (MAXRECURSION 0)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

318 Posts

Posted - 09/28/2013 :  21:55:42  Show Profile  Reply with Quote
This alternative offers OP the option to specify which companyid to to start at (also if companyid is a "subcompany"):

declare @company table (
   companyid int
  ,parentcompanyid int
);
insert into @company (companyid,parentcompanyid)
 values (1,null)
       ,(2,null)
       ,(3,null)
       ,(4,1)
       ,(5,4)
       ,(6,2)
       ,(7,5)
       ,(8,6)
       ,(9,3)
       ,(10,7)
;
with _companytree
  as (select a.companyid as rootcompanyid
            ,a.companyid
            ,b.companyid as nextcompanyid
            ,0 as level
        from @company as a
             left outer join @company as b
                          on b.parentcompanyid=a.companyid
       where a.parentcompanyid is null
          or b.companyid is null
      union all
      select b.rootcompanyid
            ,a.companyid
            ,c.companyid as nextcompanyid
            ,b.level+1 as level
        from @company as a
             inner join _companytree as b
                     on b.nextcompanyid=a.companyid
             inner join @company as c
                     on c.parentcompanyid=a.companyid
     )
    ,companytree
  as (select *
        from _companytree
       where nextcompanyid is not null
      union all
      select b.rootcompanyid
            ,a.companyid
            ,a.nextcompanyid
            ,b.level+1 as level
        from _companytree as a
             inner join _companytree as b
                     on b.nextcompanyid=a.companyid
       where a.nextcompanyid is null
     )
select c.*
  from companytree as a
       inner join companytree as b
               on b.rootcompanyid=a.rootcompanyid
              and b.level>=a.level
       inner join @company as c
               on c.companyid=b.companyid
 where a.companyid=4
;

All one has to do, is change the line marked in red, to receive the company tree from that companyid.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/29/2013 :  02:53:54  Show Profile  Reply with Quote
for starting from a companyid you just need to tweak earlier suggestion like


DECLARE @CompanyID int
SET @CompanyID = 4 -- Any value you want

;With Company_Tree
AS
(
SELECT CompanyID,ParentCompanyID,CAST(0 AS int) AS Level
FROM Company
WHERE CompanyID = @CompanyID
UNION ALL
SELECT c.CompanyID,c.CompanyParentID,ct.Level + 1
FROM Company c
INNER JOIN Company_Tree ct
ON ct.CompanyID = c.ParentCompanyID
)

SELECT *
FROM Company_Tree
ORDER BY Level

OPTION (MAXRECURSION 0)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sqlbug
Posting Yak Master

Canada
197 Posts

Posted - 09/30/2013 :  11:16:52  Show Profile  Reply with Quote
visakh and bitsmed, Thanks to both of you...they work.
woohoo...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000