How can you detect a parent with a nested relationship in a database using SQL?
I'm using Firebird 2.1. There is a table name Folders
, with the fields:
- Fol开发者_如何学JAVAderID
- ParentFolderID
- FolderName
ParentFolderID
is -1 if it's the root folder -- otherwise it contains the parent folder's ID.
How can I find all parents (up to the root folder) of a low level node?
Do I need a recursive query? (Firebird supports them)
Something like this:
WITH RECURSIVE hierarchy (folderid, ParentFolderId, FolderName) as (
SELECT folderid, ParentFolderId, FolderName
FROM folders
WHERE ParentFolderID = -1
UNION ALL
SELECT folderid, ParentFolderId, FolderName
FROM folders f
JOIN hierarchy p ON p.folderID = f.parentFolderID
)
SELECT *
FROM hierarchy
Edit: the following query will walk the hierarchy "up", finding all parents of a given folder.
WITH RECURSIVE hierarchy (folderid, ParentFolderId, FolderName) as (
SELECT folderid, ParentFolderId, FolderName
FROM folders
WHERE folderid = 42
UNION ALL
SELECT folderid, ParentFolderId, FolderName
FROM folders f
JOIN hierarchy p ON p.parentFolderID = f.folderID
)
SELECT *
FROM hierarchy
精彩评论