views:

68

answers:

1

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?

+1  A: 

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?

astander
you get +1 for that since it's half what I wanted, now if I pass B to that procedure, I need to recreate the full tree, how to find A and D?
Fredou
I'm currently looking at this http://stackoverflow.com/questions/855942/multiple-parents-tree-or-digraph-implementation-sql-server-2005
Fredou
i have changed the answer a bit, have a look, i get the roots first, the work down from there...
astander
@astander, one more thing, in your example your allowing NULL, I cannot change the database and in my database, I cannot have NULL. So how to make it work if you only remove the two insert with null?
Fredou
How do you then determine what is a root, what parentid do you have for roots?
astander
@astander, look at my question, the code that define a function
Fredou
OK, so if i understan this correctly from getRootCompagny you recursively loop until a given row where the companyparentid cannot be found? What does the entry for a "root" look like, Iassumed it would be (a, null). But what is the row in your case? does the entries start off with a as the parent, so you have no definition of the root entry?
astander
@astander, data would like like this (row): (b,a),(c,a),(c,b),(e,d),(b,e) so your right, you need to loop until there is no more match to get the root parent, there is no other way to find a root parent(i think)
Fredou
OK, made the changes, can you have a look again?
astander
thanks, working perfectly, I just started using CTE a few days ago so I just learned a tons with your solution. Didn't know it was possible to do that kind of thing.
Fredou
Glad i could help X-)
astander