Query To Retrieve All Sub Categories from a self referencing table

[MFS371]

Is there a simple way to query all the categories from a self referencing table that contains categories and sub categories all in the same table. Similar to book categories on Amazon.com. For example, a set of related categories could be: Books->Computers->Programming->T_SQL

The table being defined like

PK_CAT_ID int NOT NULL
PARENT INT ( references PK_CAT_ID or NULL )
CATEGORY_NAME CHAR NOT NULL

The data being:
1, NULL, Books
2, 1, Computers
3, 2, Programming
4, 3, T_SQL

Find all parents of T_SQL category.

 

[zhavic]

You need to use some recursion

Based on

http://support.microsoft.com/default.aspx?scid=kb;en-us;248915

/* create sample table */
CREATE TABLE categoryies(
PK_CAT_ID integer NOT NULL,
PARENT integer,
CATEGORY_NAME CHAR(20)
)
GO

/* insert sample data */
INSERT categoryies VALUES(1,NULL,'Books')
INSERT categoryies VALUES(2,1,'Computers')
INSERT categoryies VALUES(3,2,'Programming')
INSERT categoryies VALUES(4,3,'T_SQL')
GO

ALTER PROCEDURE find_parents( @sub_CATEGORY_NAME char(20) ) AS
 SET NOCOUNT ON
	
 DECLARE @lvl int, @line char(20), @category char(20), @current integer

 /* get ID of passed @sub_CATEGORY_NAME */
 SELECT @current = PARENT FROM categoryies WHERE CATEGORY_NAME = @sub_CATEGORY_NAME

 /* table for store parents of @sub_CATEGORY_NAME */
 CREATE TABLE #result ( PK_CAT_ID integer )

 CREATE TABLE #stack (item integer, lvl int)
 INSERT INTO #stack VALUES (@current, 1)
 SELECT @lvl = 1
 WHILE @lvl > 0
	BEGIN
	    IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl AND item IS NOT NULL)
	        BEGIN
	            SELECT  @current = item
	            FROM #stack
	            WHERE lvl = @lvl

				INSERT INTO #result VALUES( @current )

	            DELETE FROM #stack
	            WHERE lvl = @lvl
	                AND item = @current

	            INSERT #stack
	                SELECT PARENT, @lvl + 1
	                FROM categoryies
	                WHERE PK_CAT_ID = @current

	            IF @@ROWCOUNT > 0
                        SELECT @lvl = @lvl + 1
        END
    ELSE
        SELECT @lvl = @lvl - 1

END -- WHILE

/* get result */
SELECT categoryies.PK_CAT_ID, categoryies.CATEGORY_NAME 
	FROM categoryies
		INNER JOIN #result ON #result.PK_CAT_ID = categoryies.PK_CAT_ID
	ORDER BY categoryies.PK_CAT_ID

GO
EXEC find_parents 'T_SQL'

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[FoxyBOA]

Hi!

IMHO the easiest way add column to your table (or if it's impossible create temp table), which would describe full path to your item at the hierarchy.

All Table ... Add ItemPath varchar(...)
1, NULL, Books, '.1.'
2, 1, Computers, '.1.2.'
3, 2, Programming, '.1.2.3.'
4, 3, T_SQL, '.1.2.3.4.'


SELECT ALL elements books at category TSQL would:
SELECT * FROM ... WHERE ItemPath LIKE '%.4.%'

Please also note that recursion has limit (you can't exec call of dept can't exceed 32 calls).