MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `Folders` (
  `id` int NOT NULL,
  `type` enum('DIRECTORY','FILE') NOT NULL DEFAULT 'DIRECTORY',
  `name` varchar(150),
  `parentId` int DEFAULT NULL,
  `ownerId` int NOT NULL
);

INSERT INTO Folders VALUES (13,'DIRECTORY','Home',1,5),
 (14,'DIRECTORY','Dependants',13,5),
 (15,'DIRECTORY','Aaron Jacobs',14,5),
 (16,'FILE','Aaron Jacobs Passport.pdf',15,5),
 (17,'FILE','Aaron Jacobs ID.pdf',15,5),
 (18,'FILE','Aaron Jacobs Drivers License.pdf',15,5);

select * from Folders;



id
type
name
parentId
ownerId


13
DIRECTORY
Home
1
5

14
DIRECTORY
Dependants
13
5

15
DIRECTORY
Aaron Jacobs
14
5

16
FILE
Aaron Jacobs Passport.pdf
15
5

17
FILE
Aaron Jacobs ID.pdf
15
5

18
FILE
Aaron Jacobs Drivers License.pdf
15
5


CREATE TABLE `Documents` (
  `documentId` int,
  `folderFileId` int DEFAULT NULL,
  `documentNumber` varchar(132) 
) ;

INSERT INTO Documents VALUES (1,16,'PA123456'),(2,17,'ID123456'),(3,18,'DL123456');

SELECT * FROM Documents;


documentId
folderFileId
documentNumber


1
16
PA123456

2
17
ID123456

3
18
DL123456


SELECT 
    `subfolders2`.`name` AS `FileName`,
    `subfolders`.`name` AS `Foldername`,
    `documents`.*
FROM
    `Folders` `folder`
        JOIN
    `Folders` `subfolders` ON (`folder`.`id` = `subfolders`.`parentId`)
        AND (`folder`.`type` = 'DIRECTORY')
        AND (`folder`.`name` = 'Dependants')
        JOIN
    `Folders` `subfolders2` ON (`subfolders`.`id` = `subfolders2`.`parentId`)
         AND `subfolders2`.`type` = 'FILE'
        JOIN
    `Documents` `documents` ON (`documents`.`folderFileId` = `subfolders2`.`id`);


FileName
Foldername
documentId
folderFileId
documentNumber


Aaron Jacobs Passport.pdf
Aaron Jacobs
1
16
PA123456

Aaron Jacobs ID.pdf
Aaron Jacobs
2
17
ID123456

Aaron Jacobs Drivers License.pdf
Aaron Jacobs
3
18
DL123456

id	type	name	parentId	ownerId
13	DIRECTORY	Home	1	5
14	DIRECTORY	Dependants	13	5
15	DIRECTORY	Aaron Jacobs	14	5
16	FILE	Aaron Jacobs Passport.pdf	15	5
17	FILE	Aaron Jacobs ID.pdf	15	5
18	FILE	Aaron Jacobs Drivers License.pdf	15	5

FileName	Foldername	documentId	folderFileId	documentNumber
Aaron Jacobs Passport.pdf	Aaron Jacobs	1	16	PA123456
Aaron Jacobs ID.pdf	Aaron Jacobs	2	17	ID123456
Aaron Jacobs Drivers License.pdf	Aaron Jacobs	3	18	DL123456