February 15, 2007 at 3:38 am
Hello,
I have a CTE which shows the relationships between tables. I want to return the Ultimate parent for each child ( the level 0 parent), with each child. How can I achieve this?
CTE shown below:
;
WITH TableHierarchy (TableID, TableName, ParentID, LevelNo)
AS
(
-- Anchor member definition
SELECT P.TableID, P.TableNAme, P.PArentID,
0
AS Level
FROM tblTableRelationship AS P
WHERE ParentID IS NULL
UNION ALL
-- Recursive member definition
SELECT P.TableID, P.TableNAme, P.PArentID,
LevelNo
+ 1
FROM tblTableRelationship AS P
INNER JOIN TableHierarchy AS d
ON P.PArentID = d.TableID
)
SELECT????
thanks in advance
February 15, 2007 at 6:00 am
Can you please post how do you want to see the output data with an example so that it would be easy to understand what you are looking for.
Thanks
Prasad Bhogadi
www.inforaise.com
February 15, 2007 at 8:01 am
Hello,
thanks for your reply.
I was looking for something like this:
Object topLevelParent Level
------- --------------- ------
PropertyGroup Null 0
Property PropertyGroup 1
PropertyDetails PropertyGroup 2
Region Null 0
County Region 1
Town Region 2
February 16, 2007 at 3:02 am
You need to carry the RootIDs through the recursion. Something like:
DECLARE @t TABLE
(
TableID int NOT NULL PRIMARY KEY
,TableName varchar(20) NOT NULL
,ParentID int NULL
)
INSERT INTO @t
SELECT 1, 'PropertyGroup', NULL UNION ALL
SELECT 2, 'Property', 1 UNION ALL
SELECT 3, 'PropertyDetails', 2 UNION ALL
SELECT 4, 'Region', NULL UNION ALL
SELECT 5, 'County', 4 UNION ALL
SELECT 6, 'Town', 5
;WITH TableHierarchy (TableID, ParentID, RootID, LevelNo)
AS
(
SELECT TableID, ParentID, TableID, 0 AS LevelNo
FROM @t
WHERE ParentID IS NULL
UNION ALL
SELECT T.TableID, T.ParentID, H.RootID, H.LevelNo + 1
FROM TableHierarchy H
JOIN @t T
ON H.TableID = T.ParentID
)
SELECT T1.TableName
,CASE
WHEN T1.TableName = T2.TableName
THEN NULL
ELSE T2.TableName
END AS TopLevelParent
,H.LevelNo
FROM TableHierarchy H
JOIN @t T1
ON H.TableID = T1.TableID
JOIN @t T2
ON H.RootID = T2.TableID
ORDER BY RootID, LevelNO
-- OPTION(MAXRECURSION 3)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply