开发者

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:

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜