All geek questions in one place

How can you detect a parent with nested relationships in a database using SQL?

I am using Firebird 2.1. There is a Folders table name, with fields:

  • Folderid
  • ParentFolderID
  • Foldername

ParentFolderID is -1 if it is the root folder, otherwise it contains the identifier of the parent folder.

How can I find all parents (down to the root folder) of a low level node?

Is a recursive query needed? ( Firebird supports them )

+6
source share
1 answer

Something like that:

 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 next query will move up the hierarchy, finding all the parents of this 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
+7
source

Source: https://habr.com/ru/post/892053/

More articles:


All Articles