Reputation: 93
I have two tables Company
and CompanyRelationShip
.
DECLARE @Company TABLE (
CompanyId INT
,RootCompanyId INT
,CompanyName VARCHAR(100)
)
INSERT INTO @Company
VALUES (2,2,'ROOT')
,(106,2,'ABC')
,(105,2,'CDF')
,(3,3,'ROOT2')
,(150,3,'YXZ')
,(151,3,'XZX')
DECLARE @CompanyRelationShip TABLE (
PrimaryCompanyId INT
,CompanyId INT
)
INSERT INTO @CompanyRelationShip
VALUES (2,2)
,(2,106)
,(2,105)
,(106,105)
,(3,3)
,(3,151)
,(3,150)
,(151,150)
I want the result in the below format
CompanyId PrimayCompanyId PrimaryCompanyName RootCompanyId RootCompanyName
2 2 ROOT 2 ROOT
106 2 ROOT 2 ROOT
105 106 ABC 2 ROOT
3 3 ROOT2 3 ROOT2
151 3 ROOT2 3 ROOT2
150 151 XZX 3 ROOT2
I have tried the below query to get the result
WITH PrimayCompany
AS (
SELECT CR.PrimaryCompanyId
,C.CompanyName
FROM @CompanyRelationShip CR
JOIN @Company C ON CR.CompanyId = CR.PrimaryCompanyId
)
,RootCompany
AS (
SELECT RootCompanyId
,CompanyName
FROM @Company
WHERE CompanyId = RootCompanyId
)
SELECT C.CompanyId
,C.RootCompanyId
,RC.CompanyName
,CR.PrimaryCompanyId
,PC.CompanyName
FROM @Company C
LEFT JOIN @CompanyRelationShip CR ON C.CompanyId = CR.PrimaryCompanyId
LEFT JOIN PrimayCompany PC ON PC.PrimaryCompanyId = CR.PrimaryCompanyId
LEFT JOIN RootCompany RC ON RC.RootCompanyId = CR.PrimaryCompanyId
I would really appreciate a bit of help.
Upvotes: 2
Views: 100
Reputation: 67341
In my comment I asked you, why you would need the table @CompanyRelationShip
at all... This is just adding a hell of a lot of complexity and potentials for errors.
My suggestion relies on the first table alone. Look, how I've changed the parent IDs of 105 and 151 to place them below in the hierarchy. Just to show the principles I've added a second child below 150:
DECLARE @Company TABLE (
CompanyId INT
,RootCompanyId INT
,CompanyName VARCHAR(100)
);
INSERT INTO @Company
VALUES (2,2,'ROOT')
,(106,2,'ABC')
,(105,106,'CDF')
,(3,3,'ROOT2')
,(150,3,'YXZ')
,(151,150,'XZX')
,(152,150,'Second below 150');
--the query
WITH recCTE AS
(
SELECT CompanyId AS [RootId],CompanyName AS [RootName],*,1 AS HierarchyLevel FROM @Company WHERE CompanyId=RootCompanyId
UNION ALL
SELECT rc.RootId,rc.RootName,c.*,rc.HierarchyLevel+1
FROM @Company c
INNER JOIN recCTE rc ON c.RootCompanyId=rc.CompanyId AND c.CompanyId<>rc.CompanyId
)
SELECT RootId
,RootName
,RootCompanyId AS [PrevId]
,CompanyId
,CompanyName
,HierarchyLevel
FROM recCTE rc
ORDER BY RootId,HierarchyLevel;
The result
RootId RootName PrevId CompanyId CompanyName HierarchyLevel
2 ROOT 2 2 ROOT 1
2 ROOT 2 106 ABC 2
2 ROOT 106 105 CDF 3
3 ROOT2 3 3 ROOT2 1
3 ROOT2 3 150 YXZ 2
3 ROOT2 150 151 XZX 3
3 ROOT2 150 152 Second below 150 3
The idea in short:
RootId
and RootName
are just passed through to show up in your final set.The HierarchyLevel
is the position within the line, thus placing 105 within ROOT, but below 106.
Hope this helps...
As told, the given structure is not the best choice and should be altered. But if you have to stick to this, you might try something along this:
WITH recCTE AS
(
SELECT CompanyId AS [RootId],CompanyName AS [RootName],*,1 AS HierarchyLevel FROM @Company WHERE CompanyId=RootCompanyId
UNION ALL
SELECT rc.RootId,rc.RootName,c.*,rc.HierarchyLevel+1
FROM @Company c
INNER JOIN recCTE rc ON c.RootCompanyId=rc.CompanyId AND c.CompanyId<>rc.CompanyId
)
SELECT rc.CompanyId
,rc.CompanyName
,COALESCE(crs.PrimaryCompanyId,rc.RootCompanyId) AS ComputedPrevId
,COALESCE(c1.CompanyName,rc.RootName) AS ComputedPrevName
,rc.RootId
,rc.RootName
FROM recCTE rc
LEFT JOIN @CompanyRelationShip crs ON rc.CompanyId=crs.CompanyId AND rc.RootCompanyId<>crs.PrimaryCompanyId
LEFT JOIN @Company c1 ON crs.PrimaryCompanyId=c1.CompanyId
ORDER BY rc.RootId,rc.HierarchyLevel;
This will first use a recursive CTE to find the children below their root companies and the will try to find the corresponding line in your relationship table.
If you use just SELECT *
instead of the column list you can see the full set.
Using LEFT JOIN
will return NULL
, when the ON
claus is not met.
COALESCE
will return the first non-NULL value, so - hopefully - the one you are after.
Upvotes: 1