March 20, 2017 at 10:20 am
Hi Experts
I have a situation in which I want to avoid cursors/loops .Suppose I have a table in the below format
| Node | Parent_Node |
| A | |
| B | A |
| C | B |
| D | C |
| E | |
| F | E |
| G | F |
| H | G |
Here A and E are the parent root nodes(Lets call them root nodes) which does not have any parent node and from which all nodes originate . Now I want to find out the root node for any given node.For e.g I want output like belw
| Node | Parent_Node |
| A | A |
| B | A |
| C | A |
| D | A |
| E | E |
| F | E |
| G | E |
H E
BR
Arshad
March 20, 2017 at 10:46 am
Something like this?CREATE TABLE #EMP (EmpID CHAR(1),
MgrID CHAR(1));
GO
INSERT INTO #EMP
VALUES
('A',NULL),
('B','A'),
('C','B'),
('D','C'),
('E',NULL),
('F','E'),
('G','F'),
('H','G');
GO
SELECT *
FROM #EMP;
GO
WITH Hierachy AS(
SELECT E.EmpID,
E.EmpID AS MgrID,
E.EmpID AS MstID,
1 AS HLevel
FROM #EMP E
WHERE E.MgrID IS NULL
UNION ALL
SELECT E.EmpID,
H.EmpID AS MgrID,
H.MstID,
H.Hlevel + 1 AS HLevel
FROM #EMP E
JOIN Hierachy H ON E.MgrID = H.EmpID
)
SELECT H.EmpID,
--H.MgrID,
H.MstID
FROM Hierachy H
ORDER BY H.EmpID;
GO
DROP TABLE #EMP
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 20, 2017 at 10:57 am
Here's an option that might work better than a rCTE. This article was written from experience on a problem very similar to what you're describing.
It uses a loop, but it's a very efficient loop because it is set-based. Check it out and let me know if you have any questions. I'll try to make the author to reply :).
http://www.sqlservercentral.com/articles/set-based+loop/127670/
March 20, 2017 at 12:01 pm
Hi Thom,
Thanks for the post . But the condtion
E.MgrID = H.EmpID
will not work for Node C as its Parent Node is B which is not in H table
BR
Arshad
March 20, 2017 at 12:17 pm
ansaryarshad - Monday, March 20, 2017 12:01 PMHi Thom,Thanks for the post . But the condtion
E.MgrID = H.EmpID
will not work for Node C as its Parent Node is B which is not in H tableBR
Arshad
I don't understand what you're talking about. Thom's code works as expected and I'm sure that he tested it before posting.
What do you mean when you state that the condition will not work?
March 20, 2017 at 4:14 pm
ansaryarshad - Monday, March 20, 2017 12:01 PMHi Thom,Thanks for the post . But the condtion
E.MgrID = H.EmpID
will not work for Node C as its Parent Node is B which is not in H tableBR
Arshad
Did you actually try running the code? Seems to work for me.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 21, 2017 at 3:00 am
Luis Cazares - Monday, March 20, 2017 12:17 PMansaryarshad - Monday, March 20, 2017 12:01 PMHi Thom,Thanks for the post . But the condtion
E.MgrID = H.EmpID
will not work for Node C as its Parent Node is B which is not in H tableBR
ArshadI don't understand what you're talking about. Thom's code works as expected and I'm sure that he tested it before posting.
What do you mean when you state that the condition will not work?
Luis is right,. I did test it. Did you, ansaryarshad? If so, what were the results you got and what was the SQL you ran? Wondering if you modified it incorrectly.
On a different note, Luis, found your articule quite interesting. I avoid hierarchy data as much as I can, so I can't see any applications for myself, but if the OP does have as many users as you did, then would be worth while him making use of.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 22, 2017 at 3:52 am
Hi Thom,
Sorry for the reply.
I am coming from an SAP HANA background where CTEs are still evolving . We dont have the option of doing recursive CTE or rCTES (Frankly I was not aware that there existed something like rCTEs).
I tried to breakup your rCTE and did individual select statements.Obviously that did not work ..
BR
Arshad
March 22, 2017 at 7:10 am
ansaryarshad - Wednesday, March 22, 2017 3:52 AMHi Thom,
Sorry for the reply.
I am coming from an SAP HANA background where CTEs are still evolving . We dont have the option of doing recursive CTE or rCTES (Frankly I was not aware that there existed something like rCTEs).
I tried to breakup your rCTE and did individual select statements.Obviously that did not work ..BR
Arshad
Did you tried the method that I explained in the article? It's simple, does not require CTEs and will be very fast.
March 22, 2017 at 7:49 am
ansaryarshad - Wednesday, March 22, 2017 3:52 AMHi Thom,
Sorry for the reply.
I am coming from an SAP HANA background where CTEs are still evolving . We dont have the option of doing recursive CTE or rCTES (Frankly I was not aware that there existed something like rCTEs).
I tried to breakup your rCTE and did individual select statements.Obviously that did not work ..BR
Arshad
Are you working in SQL Server for this problem or not?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 22, 2017 at 8:22 am
May I suggest adding a column to your recursive table that stores the root value for each row. Have a trigger keep the column updated. All your subsequent queries can be simple and fast. SQL recursion is unnatural, ugly, and slow. An alternate solution is to create a separate table with root, path, levels, etcetera, all pre-calculated. Joe Celko's book on hierarchies is quite good.
March 22, 2017 at 10:51 pm
If you want to go whole hog on hierarchies, see the following articles
http://www.sqlservercentral.com/articles/Hierarchy/94040/
http://www.sqlservercentral.com/articles/T-SQL/94570/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply