Question

What is a tree based system and how can you illustrate a tree-based system with a SQL query? Please be detailed. Thanks!

Answer 1

SQL QUERY FOR TREE

We have some tree structures stored in SQL Server and use the technique mentioned above to store a node's hierarchy in the record. i.e.

tblTreeNode

TreeID = 1

TreeNodeID = 100

ParentTreeNodeID = 99

Hierarchy = ".33.59.99.100."

[...] (actual data payload for node)

FOR SELECTION

SELECT * FROM tblNode WHERE Hierarchy LIKE '%.100.%'

Here's the insert trigger:

--Setup the top level if there is any

UPDATE T RIGGER

SET T.TreeNodeHierarchy = '.' + CONVERT(nvarchar(10), T.TreeNodeID) + '.'

FROM tblTreeNode AS T

INNER JOIN inserted i ON T.TreeNodeID = i.TreeNodeID

WHERE (i.ParentTreeNodeID IS NULL) AND (i.TreeNodeHierarchy IS NULL)

WHILE EXISTS (SELECT * FROM tblTreeNode WHERE TreeNodeHierarchy IS NULL)

BEGIN

--Update those items that we have enough information to update - parent has text in Hierarchy

UPDATE CHILD

SET CHILD.TreeNodeHierarchy = PARENT.TreeNodeHierarchy + CONVERT(nvarchar(10),CHILD.TreeNodeID) + '.'

FROM tblTreeNode AS CHILD

INNER JOIN tblTreeNode AS PARENT ON CHILD.ParentTreeNodeID = PARENT.TreeNodeID

WHERE (CHILD.TreeNodeHierarchy IS NULL) AND (PARENT.TreeNodeHierarchy IS NOT NULL)

END

FOR UPDATE TRIGGER:

--Only want to do something if Parent IDs were changed--Only want to do something if

IF UPDATE(ParentTreeNodeID)

BEGIN

--Update the changed items to reflect their new parents

UPDATE CHILD

SET CHILD.TreeNodeHierarchy = CASE WHEN PARENT.TreeNodeID IS NULL THEN '.' + CONVERT(nvarchar,CHILD.TreeNodeID) + '.' ELSE PARENT.TreeNodeHierarchy + CONVERT(nvarchar, CHILD.TreeNodeID) + '.' END

FROM tblTreeNode AS CHILD

INNER JOIN inserted AS I ON CHILD.TreeNodeID = I.TreeNodeID

LEFT JOIN tblTreeNode AS PARENT ON CHILD.ParentTreeNodeID = PARENT.TreeNodeID

--Now update any sub items of the changed rows if any exist

IF EXISTS (

SELECT *

FROM tblTreeNode

INNER JOIN deleted ON tblTreeNode.ParentTreeNodeID = deleted.TreeNodeID

)

UPDATE CHILD

SET CHILD.TreeNodeHierarchy = NEWPARENT.TreeNodeHierarchy + RIGHT(CHILD.TreeNodeHierarchy, LEN(CHILD.TreeNodeHierarchy) - LEN(OLDPARENT.TreeNodeHierarchy))

FROM tblTreeNode AS CHILD

INNER JOIN deleted AS OLDPARENT ON CHILD.TreeNodeHierarchy LIKE (OLDPARENT.TreeNodeHierarchy + '%')

INNER JOIN tblTreeNode AS NEWPARENT ON OLDPARENT.TreeNodeID = NEWPARENT.TreeNodeID

END

one more bit, a check constraint to prevent a circular reference in tree nodes:

FOR ALTERING:

ALTER TABLE [dbo].[tblTreeNode] WITH NOCHECK ADD CONSTRAINT [CK_tblTreeNode_TreeNodeHierarchy] CHECK  

((charindex(('.' + convert(nvarchar(10),[TreeNodeID]) + '.'),[TreeNodeHierarchy],(charindex(('.' + convert(nvarchar(10),[TreeNodeID]) + '.'),[TreeNodeHierarchy]) + 1)) = 0))