how would you get many top root?

Question

I have a parent/child relation table, many to many

parentid int not null
childid int not null

let say I have company a, b, c, d, e

a
..b
....c
..c
d
..e
....b
......c

I got this query to return one top parent

FUNCTION [dbo].[getRootCompagny] 
(
    @root int
)
RETURNS int
AS
BEGIN
DECLARE @parent int

SELECT @parent = companyparentid from companyrelation where companychildid = @root
if @parent is not null
   set @root = dbo.getRootCompagny(@parent)

RETURN @root
END

If I pass b, I will only get

a
..b
....c
..c

because the query I wrote can only manage one top parent. How would you fix it to be able to get the whole tree, like the first one?

here is my CTE

    PROCEDURE [dbo].[GetCompanyRelation]
 @root int
AS
BEGIN
 SET NOCOUNT ON;

            set @root = dbo.getRootCompagny(@root)

 WITH cieCTE(CompanyChildid, CompanyParentid, depth, sortcol)
  AS
  (
   -- root member
   SELECT @root
     , null 
     , 0
     , CAST(@root AS VARBINARY(900))


   UNION ALL

   -- recursive member
   SELECT R.CompanyChildid
     , R.CompanyParentid
     , C.depth+1
     , CAST(sortcol + CAST(R.CompanyChildid AS BINARY(4)) AS VARBINARY(900))
   FROM CompanyRelation AS R JOIN cieCTE AS C ON R.CompanyParentid = C.CompanyChildid
  )

  -- outer query
  SELECT cieCTE.depth
    , cieCTE.CompanyChildid as ChildID
    , cieCTE.CompanyParentid as ParentId
    , company.[name] as [Name]
  FROM cieCTE inner join company on cieCTE.CompanyChildid = company.companyid
  ORDER BY sortcol
END

in the end, with the logic above, I get a, how to get a,d?

Answer 1

OK then, will you have a look at this. It gives the indents and a full tree path

    DECLARE @Table TABLE(
     ID VARCHAR(10),
     ParentID VARCHAR(10)
)

--INSERT INTO @Table (ID,ParentID) SELECT 'a', NULL
INSERT INTO @Table (ID,ParentID) SELECT 'b', 'a'
INSERT INTO @Table (ID,ParentID) SELECT 'c', 'a'
INSERT INTO @Table (ID,ParentID) SELECT 'c', 'b'

--INSERT INTO @Table (ID,ParentID) SELECT 'd', NULL
INSERT INTO @Table (ID,ParentID) SELECT 'e', 'd'
INSERT INTO @Table (ID,ParentID) SELECT 'b', 'e'

DECLARE @Start VARCHAR(10)
SELECT @Start = 'e'

;WITH   roots AS (
     SELECT *
     FROM @Table
     WHERE ID = @Start
     UNION ALL
     SELECT  DISTINCT
       NULL,
       ParentID
     FROM @Table
     WHERE ParentID = @Start
     AND  ParentID NOT IN ( SELECT ID FROM @Table)
     UNION ALL
     SELECT t.*
     FROM @Table t INNER JOIN
       roots r ON t.ID = r.ParentID
)
,    layers AS(
     SELECT ParentID AS ID,
       CAST(NULL AS VARCHAR(10)) AS ParentID,
       CAST('' AS VARCHAR(MAX)) AS DisplayDepth,
       CAST(ParentID + '\' AS VARCHAR(MAX)) AS LayerPath
     FROM roots
     WHERE ParentID NOT IN ( SELECT ID FROM @Table)
     UNION ALL
     SELECT t.*,
       DisplayDepth + '[-]',
       LayerPath + t.ID + '\'
     FROM @Table t INNER JOIN
       layers l ON t.ParentID = l.ID
)
SELECT  *
FROM    layers
ORDER BY LayerPath

Is that what you had in mind?