Recursive Query Without Self-Referencing Structure

SeanAleks


I need a statement that can return a hierarchy of folders. There are a number of good articles out there describing how to do this in SQL 2005 with a Common Table Expression (CTE) that references itself under a UNION ALL. Yet every example I come across involves a self-referencing table. Unfortunately the architect of my project did not use that simple structure. Instead, we have a Folder table and Folder Properties table. Creating a child Folder involves adding a row in the Properties table that links to a parent. Here's the statement I came up with:

WITH Folders (ID, ParentID, FolderName, FolderDepth)
AS (
SELECT FOLDER_ID, NULL, FOLDER_NAME, 0
FROM ZZ_FOLDER
WHERE FOLDER_ID = 1
UNION ALL
SELECT F.FOLDER_ID, Folders.ID, F.FOLDER_NAME, Folders.FolderDepth + 1
FROM ZZ_FOLDER F INNER JOIN ZZ_FOLDER_PROP FP ON FP.FPROP_FOLDER_ID = F.FOLDER_ID
WHERE FP.FPROP_LINK_ID = Folders.ID
)
SELECT * FROM Folders

The initial statement brings back the top of the folder structure. The recursive statement brings back Folder rows that have an entry in their Property rows for the parent. Yet when I try to execute this, I get:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Folders.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Folders.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Folders.FolderDepth" could not be bound.

It sounds like all the recursive references to the CTE are unavailable. What am I doing wrong Would welcome any suggestions, too. I realize this isn't a T-SQL specific question, more SQL in general, but I didn't see a more relevant forum. What follows are some tables/data to run the above statement against.

-- Create Folder Tables
CREATE TABLE ZZ_FOLDER (
FOLDER_ID INTEGER NOT NULL PRIMARY KEY,
FOLDER_NAME VARCHAR(50))
CREATE TABLE ZZ_FOLDER_PROP (
FPROP_ID INTEGER NOT NULL PRIMARY KEY,
FPROP_FOLDER_ID INTEGER NOT NULL,
FPROP_LINK_TYPE VARCHAR(50),
FPROP_LINK_ID INTEGER NOT NULL)
GO
-- Populate Folder Tables
INSERT INTO ZZ_FOLDER VALUES (1, 'Top Level')
INSERT INTO ZZ_FOLDER VALUES (2, 'Second Level A')
INSERT INTO ZZ_FOLDER VALUES (3, 'Second Level B')
INSERT INTO ZZ_FOLDER VALUES (4, 'Second Level C')
INSERT INTO ZZ_FOLDER VALUES (5, 'Third Level A1')
INSERT INTO ZZ_FOLDER VALUES (6, 'Third Level A2')
INSERT INTO ZZ_FOLDER VALUES (7, 'Third Level A3')
INSERT INTO ZZ_FOLDER VALUES (8, 'Third Level B1')
INSERT INTO ZZ_FOLDER VALUES (9, 'Third Level C1')
INSERT INTO ZZ_FOLDER VALUES (10, 'Third Level C2')
INSERT INTO ZZ_FOLDER VALUES (11, 'Fourth Level A3A')
INSERT INTO ZZ_FOLDER_PROP VALUES (1, 2, 'Folder', 1)
INSERT INTO ZZ_FOLDER_PROP VALUES (2, 3, 'Folder', 1)
INSERT INTO ZZ_FOLDER_PROP VALUES (3, 4, 'Folder', 1)
INSERT INTO ZZ_FOLDER_PROP VALUES (4, 5, 'Folder', 2)
INSERT INTO ZZ_FOLDER_PROP VALUES (5, 6, 'Folder', 2)
INSERT INTO ZZ_FOLDER_PROP VALUES (6, 7, 'Folder', 2)
INSERT INTO ZZ_FOLDER_PROP VALUES (7, 8, 'Folder', 3)
INSERT INTO ZZ_FOLDER_PROP VALUES (8, 9, 'Folder', 4)
INSERT INTO ZZ_FOLDER_PROP VALUES (9, 10, 'Folder', 4)
INSERT INTO ZZ_FOLDER_PROP VALUES (10, 11, 'Folder', 7)
GO
/*
Top Level
Second Level A
Third Level A1
Third Level A2
Third Level A3
Fourth Level A3A
Second Level B
Third Level B1
Second Level C
Third Level C1
Third Level C2
*/

Don Vilen

You need to mention the Folders 'table' in your second FROM clause, and likely give it an alias (perhaps 'f').

Here's an example from BOL .. and you're missing the equivalent of the "DirectReports d" portion in the second FROM/JOIN that tells the UNION ALL what to match from those rows already retrieved:

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
GO

Don

SeanAleks

That's exactly what it was. Thanks for the lightening-fast catch Don!

The second statement had the join in the where clause but no "real" reference to the parent. Ended up with this:

Code Snippet

WITH Folders (ID, ParentID, FolderName, FolderDepth)
AS (
SELECT FOLDER_ID, NULL, FOLDER_NAME, 0
FROM ZZ_FOLDER
WHERE FOLDER_ID = 1
UNION ALL
SELECT F.FOLDER_ID, FS.ID, F.FOLDER_NAME, FS.FolderDepth + 1
FROM Folders FS, ZZ_FOLDER F INNER JOIN ZZ_FOLDER_PROP FP ON FP.FPROP_FOLDER_ID = F.FOLDER_ID
WHERE FP.FPROP_LINK_ID = FS.ID
)
SELECT * FROM Folders

Thanks again!