March 4, 2016 at 7:19 pm
I need a query to get Parent, childrens,grand childrens,greatgrand childrens etc relationship upto nth level (data i have is more than 100th level.)
Here is sample data..
Table:parentmaster
ID Name
---- ----------
8571 File/sam
5475 Folder/Pat
6808 path/test
7591 file/test2
4485 Pr/dsn/
Table:Tree
Parent child
------ ------
8571 5475
8571 6808
8571 7591
5475 4485
Expected output
Table:Treedesc
Parent child Parentchildtree
------ ----- ----------------
8571 5475 File/sam-->Folder/Pat
8571 6808 File/sam-->path/test
8571 7591 File/sam-->file/test2
5475 4485 File/sam-->Folder/Pat-->Pr/dsn/
Thanks for your help in advance
March 4, 2016 at 8:28 pm
March 5, 2016 at 6:06 am
Thanks for the response. This was helpful but my database has 2 tables where the provided the link has a single table which has all the names, ids in single table.
I was not getting the expected output..
March 5, 2016 at 7:13 am
Mvs2k11 (3/5/2016)
Thanks for the response. This was helpful but my database has 2 tables where the provided the link has a single table which has all the names, ids in single table.I was not getting the expected output..
So in your case, you will need to change the recursive CTE from using a single table to using a join between the two tables. The anchor part would just join the tree table and the other table to ind the "root" nodes; the recursive part would join the CTE itself to the tree to find the next step, and join that again to the other table to find the description, which then should also be concatenated to the description from the CTE.
If you understand how the recursive CTE works, and you understand how a normal joins works, you should be able to solve this.
If you need further help, then please post the tabbles used (as CREATE TABLE statements), a small but illustrative selection of sample data (as INSERT statements), the expected results for that sample data, and the work you already done so far - then we can look at where yoou are stuck and help you get over that bump.
March 5, 2016 at 8:30 am
Here is the query...
CREATE TABLE #Treedesc
(
ID BIGINT NOT NULL,
Name Varchar(500) NOT NULL,
CONSTRAINT PK_Tree
PRIMARY KEY CLUSTERED ID)
);
INSERT INTO #Treedesc
(ID, Name)
SELECT 8571, 'File/sam' UNION ALL
SELECT 5475, 'Folder/Pat'UNION ALL
SELECT 6808, 'path/test'UNION ALL
SELECT 7591, 'file/test2'UNION ALL
SELECT 4485, 'Pr/dsn/'
;
CREATE TABLE #Tree
(
ParentID BIGINT NOT NULL,
ChildID INT NULL,
CONSTRAINT PK_Tree
PRIMARY KEY CLUSTERED ParentID,ChildID)
);
INSERT INTO #Tree
(ParentID, ChildID)
SELECT 8571, 5475 UNION ALL
SELECT 8571, 6808 UNION ALL
SELECT 8571, 7591 UNION ALL
SELECT 5475, 4485
;
WITH
cteHierarchy AS
(
--Get the top level
SELECT c.ChildID, a.ParentID, b.Name, Level = 1,
HierarchicalPath = CAST('-->'+CAST(Name AS VARCHAR(500)) AS VARCHAR(4000))
FROM #Tree a inner join #Treedesc b on a.ParentID = b.ID
inner join #Treedesc c on a.childID = c.ID
UNION ALL
--Get the recursive level
SELECT e.ChildID, e.ParentID, e.Name, Level = d.Level + 1,
HierarchicalPath = CAST(d.HierarchicalPath + '-->'+CAST(e.Name AS VARCHAR(500)) AS VARCHAR(4000))
FROM #Tree e
INNER JOIN cteHierarchy d ON e.ParentID = d.ChildID
)
SELECT ChildID, ,
ParentID ,
Name = SPACE((Level-1)*4) + Name,
Level,
HierarchicalPath
FROM cteHierarchy
ORDER BY HierarchicalPath
March 5, 2016 at 9:46 am
I had to do some fixing to your CREATE TABLE scripts (suggestion - next time when you post a question, test the scripts first). And I changed the data types of the IDs to all be int, becuase mixing bigint and int for columns that reference each other is a bad idea. I did not add a foreign key between the tables, but I suggest that you do.
I then modified the CTE code to return the data you requested:
CREATE TABLE #Treedesc
(
ID INT NOT NULL,
Name Varchar(500) NOT NULL,
CONSTRAINT PK_TreeDesc
PRIMARY KEY CLUSTERED (ID)
);
INSERT INTO #Treedesc
(ID, Name)
SELECT 8571, 'File/sam' UNION ALL
SELECT 5475, 'Folder/Pat'UNION ALL
SELECT 6808, 'path/test'UNION ALL
SELECT 7591, 'file/test2'UNION ALL
SELECT 4485, 'Pr/dsn/'
;
CREATE TABLE #Tree
(
ParentID INT NOT NULL,
ChildID INT NOT NULL,
CONSTRAINT PK_Tree
PRIMARY KEY CLUSTERED (ParentID,ChildID)
);
INSERT INTO #Tree
(ParentID, ChildID)
SELECT 8571, 5475 UNION ALL
SELECT 8571, 6808 UNION ALL
SELECT 8571, 7591 UNION ALL
SELECT 5475, 4485
;
WITH cteHierarchy
AS (-- Anchor query: root level with all its children
SELECT c.ParentID AS Parent, c.ChildID AS Child, CAST(r.Name + '-->' + cD.Name AS varchar(MAX)) AS ParentChildTree
FROM #Treedesc AS r -- root
INNER JOIN #Tree AS c -- children
ON c.ParentID = r.ID
INNER JOIN #Treedesc AS cD -- children's description
ON cD.ID = c.ChildID
WHERE NOT EXISTS -- verify it's a root: no ascendants
(SELECT *
FROM #Tree AS a -- ascendant
WHERE a.ChildID = r.ID)
-- Recursive part: children of previously added nodes
UNION ALL
SELECT c.ParentID, c.ChildID, p.ParentChildTree + '-->' + cD.Name
FROM cteHierarchy AS p -- parent
INNER JOIN #Tree AS c -- children
ON c.ParentID = p.Child
INNER JOIN #Treedesc AS cD -- children's description
ON cD.ID = c.ChildID)
SELECT * FROM cteHierarchy;
I have added some comments, to help you understand how the process works. I hope that with this, you'll be able to maintain the code in the future.
March 5, 2016 at 10:27 am
Thanks a lot..
The provided query works fine however when I ran the query against with 1 million records which is failing due to maximum recursion error and I have added option (maxrecursion 32767) but still faliing..
Error:the statement terminated..The maximum recursion has been exhausted before the statement completion.
Please help..
March 5, 2016 at 12:11 pm
Mvs2k11 (3/5/2016)
Thanks a lot..The provided query works fine however when I ran the query against with 1 million records which is failing due to maximum recursion error and I have added option (maxrecursion 32767) but still faliing..
Error:the statement terminated..The maximum recursion has been exhausted before the statement completion.
Please help..
https://msdn.microsoft.com/en-us/library/ms181714.aspx
"When 0 is specified, no limit is applied."
March 7, 2016 at 1:17 pm
Thanks a lot Hugo Kornelis
However i have ran against the database whenever verified after the results it has lot of duplicate records showing in the result set..
Can you help please..
March 7, 2016 at 1:50 pm
Please post a repro script. Not with millions of lines - try to create a repro script that is as small as possible, yet shows the same problem you are seeing.
March 8, 2016 at 3:30 pm
This is a case where you can run into a infinite loop if you had something like
PARENT CHILD
-------- -------
1 2
2 3
3 1
You end up with 1 --> 2 -->3 -->1 -->2 and so on. This is why the max recursion protection is there so I would not necessarily override it unless you are certain you dont have this situation in your data.
----------------------------------------------------
March 8, 2016 at 5:31 pm
Thanks ..I was figuring out the data which has millions of records however I have the data which have mentioned..
What is the solution.. when I put max recursion to 0 which was processing for long and I had to cancel the query..
Please suggest..
March 8, 2016 at 6:05 pm
Mvs2k11 (3/8/2016)
Thanks ..I was figuring out the data which has millions of records however I have the data which have mentioned..What is the solution.. when I put max recursion to 0 which was processing for long and I had to cancel the query..
Please suggest..
Solution is easy - check a child record you're about to add has already been added before.
Stop looping if such record already exists.
Using Hugo's script:
-- Recursive part: children of previously added nodes
UNION ALL
SELECT c.ParentID, c.ChildID, p.ParentChildTree + '-->' + cD.Name
FROM cteHierarchy AS p -- parent
INNER JOIN #Tree AS c -- children
ON c.ParentID = p.Child
INNER JOIN #Treedesc AS cD -- children's description
ON cD.ID = c.ChildID)
WHERE NOT EXISTS (select * from cteHierarchy AS e
where e.Child = c.ChildID)
_____________
Code for TallyGenerator
March 9, 2016 at 8:12 am
Thanks Sergiy !
I have tried to modify the change you mentioned but I got an error..
Msg 253, Level 16, State 1, Line 1
Recursive member of a common table expression 'cteHierarchy' has multiple recursive references.
Please suggest
March 9, 2016 at 11:07 am
Sergiy (3/8/2016)
Mvs2k11 (3/8/2016)
Thanks ..I was figuring out the data which has millions of records however I have the data which have mentioned..What is the solution.. when I put max recursion to 0 which was processing for long and I had to cancel the query..
Please suggest..
Solution is easy - check a child record you're about to add has already been added before.
Stop looping if such record already exists.
Using Hugo's script:
-- Recursive part: children of previously added nodesUNION ALL
SELECT c.ParentID, c.ChildID, p.ParentChildTree + '-->' + cD.Name
FROM cteHierarchy AS p -- parent
INNER JOIN #Tree AS c -- children
ON c.ParentID = p.Child
INNER JOIN #Treedesc AS cD -- children's description
ON cD.ID = c.ChildID)
WHERE NOT EXISTS (select * from cteHierarchy AS e
where e.Child = c.ChildID)
THis code will not work. Did you check this before posting? I dont think you can reference the CTE more than once in the recursion portion.
----------------------------------------------------
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply