6

I'm using Firebird 2.1. There is a table name Folders, with the fields:

  • FolderID
  • 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)

RacerNerd
  • 1,579
  • 1
  • 13
  • 31
Steve
  • 2,510
  • 4
  • 34
  • 53

1 Answers1

9

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
  • I think the query you posted returns all childs of a parent node. Do you have a query that finds all parents of a child node? Thanks. – Steve Jul 06 '11 at 05:46
  • You just need to "reverse" the starting condition and the "traversal" condition. See my edit –  Jul 06 '11 at 06:49
  • Thank you this works! Could you please help with one more thing: I'm using the List command to put the foldernames together in a string (Child 2 / Child 1 / Parent). How can I reverse the result of the query so it will look like: Parent / Child 1 / Child 2? – Steve Jul 06 '11 at 21:00
  • Check out this posting in the FB mailing list: http://tech.groups.yahoo.com/group/firebird-support/message/106989 –  Jul 07 '11 at 10:27