Link to home
Create AccountLog in
Avatar of n_srikanth4
n_srikanth4Flag for India

asked on

Traversing bottom to top in Tree hierarchy

Hi Experts ,
 
  I want to traverse from level 6 to Level 3 in the Organizational Tree Hierarchy .

I have a path like this with $ delimitter
for Example  $91275$91274$91273$91272 . Please find the table below for details.

CHILD      PARENT      LEVEL
91275      91274      6
91274      91273      5
91273      91272      4
91272      91270      3

Data base table and columnsTable : Organization_unit
Columns:  
Child -->ID_OU  (Leaf Node)
Parnet-->ID_OU_PARNT (Immediate Parent)
LEVEL-->ID_LVL_OU (Level of Leaf Node)
NAME-->NM_OU (Name of Leaf Node)
PATH-->PATH_OU (this is complete path of the Leaf Node)

Please post me an example t-sql query to traverse from level 6 to level 3 (bottom to top )for a given leaf node. Will appreciate your help plz.

Regards,
SRK.
Avatar of Neil Russell
Neil Russell
Flag of United Kingdom of Great Britain and Northern Ireland image

Such a poor database design probably leads to your total lack of responses.  Why are you trying to store a binary tree in a database? How many records in total are you storing?
Would be far easier to pull the information out into an application and use conventional Binary Tree coding techniques.

OR
Restructure your database such that it uses A join table to represent the parent child relationship and uses database tecniques.
One or the other but trying to both in one just doesnt go.
P.S.
Assumption is Binary tree BUT whatever kind of tree, the application layer would be far far better at handling the logic in my opinion.
Avatar of Mike McCracken
Mike McCracken

Tree represented in his example data and structure is definitely not binary

mlmcc
ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Just reverse the order
Here

;with CTE(ID_OU, ID_OU_PARNT, ID_LVL_OU)
AS
(
	SELECT F.ID_OU, F.ID_OU_PARNT, F.ID_LVL_OU
	FROM TableName F
	WHERE ID_LVL_OU = 6
	UNION ALL
	SELECT F.ID_OU, F.ID_OU_PARNT, F.ID_LVL_OU
	FROM TableName F
	INNER JOIN CTE FH ON FH.ID_OU_PARNT = F.ID_OU AND F.ID_LVL_OU >= 3
)

SELECT *
FROM CTE

Open in new window

Avatar of n_srikanth4

ASKER

GOOD
GOOD