Introduction
Often our solutions require implementing a self referencing table, where records are related to the records of the same table, creating a parent-child based hierarchy. In the most generic way, it is achieved through a field “PARENT_ID
” which has an N to 1 relationship with the Primary Key field in the table. For example, the folder (directory) structure in your PC is structured this way.
In the normal programming setting where we would use a binary tree, reading data from this type of a structure involves recursive traversal. However, there is no recursive SQL in conventional terms. There are special commands in various versions of SQL (such as WITH
in SQL 2005) which facilitate traversing parent-child relations. However, often, we may need as much independence from the DBMS as possible.
Background
While the idea is not new, I was surprised that I could not find a ready solution which would be simple and did not use special functions of a particular DBMS. After searching the web and analyzing some similar solutions, I came up with my own.
While I cannot present it in the way I used it, here I present the adapted generic version, which can be modified for use in any setting.
Using the code
It's just SQL which works in MS SQL Server 2000. This article is for intermediate to advanced programmers, and thus you should be able to know where to plug the code. Moreover, this article presents an idea rather than plug and play code.
CREATE TABLE FOLDER
(
FOLDER_ID int,
FOLDER_DESCRIPTION char(128),
PARENT_ID int
)
CREATE PROCEDURE [dbo].[p_FOLDERSelectTree]
@FOLDER_ROOT_ID int
AS
SET NOCOUNT ON
CREATE TABLE #CHILDREN(FOLDER_ID int );
DECLARE @NewInsertCount int;
INSERT INTO #CHILDREN(FOLDER_ID)
SELECT FOLDER_ID FROM FOLDER WHERE FOLDER_ID = @FOLDER_ROOT_ID;
SET @NewInsertCount = @@ROWCOUNT;
WHILE @NewInsertCount > 0
BEGIN
INSERT INTO #CHILDREN(FOLDER_ID)
SELECT FOLDER_ID FROM FOLDER WHERE EXISTS
(SELECT FOLDER_ID FROM #CHILDREN
WHERE FOLDER.PARENT_ID = #CHILDREN.FOLDER_ID)
AND NOT EXISTS
(SELECT FOLDER_ID FROM #CHILDREN
WHERE FOLDER.FOLDER_ID = #CHILDREN.FOLDER_ID);
SET @NewInsertCount = @@ROWCOUNT;
END
SELECT * FROM #CHILDREN ORDER BY FOLDER_ID;
Now, whenever you need to use the tree from another Stored Procedure, you have to do the following:
CREATE TABLE #CHILDREN (FOLDER int);
INSERT INTO #CHILDREN EXEC p_FOLDERSelectTree
@FOLDER _ROOT_ID = @IN_FOLDER_ID;
"And though I have the gift of prophecy, and understand all mysteries, and all knowledge; and though I have all faith, so that I could remove mountains, and have not charity, I am nothing. And though I bestow all my goods to feed the poor, and though I give my body to be burned, and have not charity, it profiteth me nothing. Charity suffereth long, and is kind; charity envieth not; charity vaunteth not itself, is not puffed up, Doth not behave itself unseemly, seeketh not her own, is not easily provoked, thinketh no evil; Rejoiceth not in iniquity, but rejoiceth in the truth; Beareth all things, believeth all things, hopeth all things, endureth all things."