SQL query root parent child records

Question

Hi, We have nested folders with parent-child relationship. We use MySQL MyISAM DB. The data is stored in the DB in the following manner.

Every time a child folder is created in the nested structure, the previous parentID is added. I want to get the RootFolderID of a folder which is added in the hierarchy as tabulated below.

FoldID  ParentID |RootFolderID
-----------------|-------------------
1         0      | 0
2         1      | 1
3         2      | 1
4         3      | 1
5         4      | 1

Please let me know how to get the root folderID and populate it in the RootFolderID column after a folder is created each time.

Thanks.

Answer 1

if you know the parent folder's information, then the root folder will be the same as that of the parent. or, if the parent has no root folder, then the root folder will be the parent folder itself.