SQL Query Recursion for Parent Child relationship

Question

How can I query only the folders that have files from the file table using CTE Recursion?

[Folder_Table]

folder_id |parent_id |folder_name
1         |0         |Folder1
2         |1         |Folder2
3         |1         |Folder3
4         |2         |Folder4
5         |2         |Folder5
6         |3         |Folder6
7         |6         |Folder7
8         |0         |Folder8
9         |8         |Folder9
10        |8         |Folder10

[File_Table]

file_id   |folder_id |file_name
1         |4         |File1
2         |4         |File2
3         |5         |File3
4         |5         |File4
5         |9         |File5
6         |10        |File6

_______________________________________
Result (for all folders)

[+] Folder1
    [+] Folder2
        [+] Folder4
              File1
              File2
        [+] Folder5
              File3
              File4
    [+] Folder3
        [+] Folder6
            [+] Folder7
[+] Folder8
    [+] Folder9
          File5
    [+] Filder10
          File6
_______________________________________

I only want to retrieve the rows from the folder table that have files at the end of the chain. So in this case the query should give me:

folder_id |parent_id |folder_name
1         |0         |Folder1
2         |1         |Folder2
4         |2         |Folder4
5         |2         |Folder5
8         |0         |Folder8
9         |8         |Folder9
10        |8         |Folder10

Since Folder7 does not contain any files then i would not want Folder7,Folder6, or Folder3 returned in the result set.

Answer 1

It may not be the most elegant solution:

WITH cte(folder_id, parent_id, name)
AS
(
    select [folder].folder_id, parent_id, name 
    from [folder] 
        join [file] on [folder].[folder_id] = [file].[folder_id]
    union all
    select [folder].[folder_id], [folder].parent_id, [folder].name 
    from cte 
        join [folder] on cte.parent_id = folder.folder_id

)
SELECT distinct * FROM cte