OQL is not like xpath you have some limitation.
Did you already checked the playground app : https://service.mendixcloud.com/p/OQL
It helps to understand the OQL a bit better.
select Name from MyFirstModule.Folder f
join f/MyFirstModule.Folder_SubFolder/MyFirstModule.Folder a;
That would be:
from yourdomain.Folder F
join F/yourdomain.Folder_SubFolder/yourdomain.Folder SubF
You will get a denormalized, flat file. So not grouped by folder:
- folder a, subfolder x
- folder a, subfolder y
- folder a, subfolder z
- folder b, subfolder g
Also, you probably need the association the other way to have one folder have many subfolder.