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?