Query for updating a node in a hierarchial tree structure
In this article, I will demonstrate several ways of creating such a query, one that returns selected nodes and their ancestors, given a specific hierarchical relation between records.
The solutions I show make use of Oracle 9i features – the sys_connect_by_path operator and the combination of hierarchical queries and joins in a single query, which was not allowed prior to 9i. This table has three columns of interest: EMPNO (primary key), ENAME (display label) and MGR (self referencing foreign key).
I am working from 2 web pages, one is from My SQL: Managing Hierarchical Data in My SQL ( I am trying to do is return the structure in XML and that is getting confusing for me.
I was recently facing a situation where I wanted to retrieve a number of nodes from a tree-like data structure, based on certain criteria.
I wanted to present the resulting nodes again in a tree like fashion.
Al tough it lists all the rows with their correct level in the hierarchy, it does not list it in the order in which the question asked them to be. Imagine a 2 table setup with facebook type of data. Post Id is auto increment and obviously in your interface, you will sort DESC to have the latest post at the top. Table 2 This table Comment Id is the primary key, auto number.
Would it be possible to list the rows in the correct order as per the question? In your gui, you want to display it ASC, so that when reading the thread, it makes sense.
ask.sqlservercentral.com/questions/48518/…WITH cte AS ( SELECT [ICFilter ID], [Parent ID], [Filter Desc], [Active], CAST(0 AS varbinary(max)) AS Level FROM [dbo].[ICFilters] WHERE [Parent ID] = 0 UNION ALL SELECT i.[ICFilter ID], i.[Parent ID], i.[Filter Desc], i.[Active], Level CAST(i.[ICFilter ID] AS varbinary(max)) AS Level FROM [dbo].[ICFilters] i INNER JOIN cte c ON c.[ICFilter ID] = i.[Parent ID] ) SELECT [ICFilter ID], [Parent ID], [Filter Desc], [Active] FROM cte ORDER BY [Level]; This is exactly what I needed! I was definitely making it harder than it needed to be... Parent Id ) SELECT * FROM Comments ORDER BY Sortkey On F/B post 105, there was a two comment (Comment Ids 1 and 2) Someone then replied on Comment1 (Comment Id 5, Parent Id 1), and then someone else commented on that reply, so on Comment5 (Comment Id 6, Parent Id 6) And viola, the sequence is correct, under the post, you can now show the comments in the right sequence.