SQL Query Recursion for Parent Child Relationship

Question

How can I only request file folders from a 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 get rows from a folder table that have files at the end of the chain. Therefore, in this case, the request 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 files, I would not want Folder7, Folder6, or Folder3 files to be returned in the result set.

Answer 1

A slightly different way, including all columns at the end

 ;WITH q AS ( SELECT ft.folder_id FROM File_Table ft INNER JOIN Folder_Table f ON f.folder_id = ft.folder_id UNION ALL SELECT f.parent_id FROM Folder_Table f INNER JOIN q ON q.folder_id = f.folder_id ) SELECT DISTINCT f.folder_id , f.parent_id , f.folder_name , f.is_active FROM q INNER JOIN Folder_Table f ON f.folder_id = q.folder_id WHERE f.is_active = 1 

Test script

 ;WITH Folder_Table (folder_id, parent_id, folder_name, is_active) AS ( SELECT * FROM (VALUES (1, 0, 'Folder1', 1) , (2, 1, 'Folder2', 0) , (3, 1, 'Folder3', 1) , (4, 2, 'Folder4', 0) , (5, 2, 'Folder5', 0) , (6, 3, 'Folder6', 1) , (7, 6, 'Folder7', 1) , (8, 0, 'Folder8', 1) , (9, 8, 'Folder9', 1) , (10, 8, 'Folder10', 1) ) a (b, c, d, e) ) , File_Table (filed_id, folder_id, file_name) AS ( SELECT * FROM (VALUES (1, 4, 'File1') , (2, 4, 'File2') , (3, 5, 'File3') , (4, 5, 'File4') , (5, 9, 'File5') , (6, 10, 'File6') ) a (b, c, d) ) , q AS ( SELECT ft.folder_id FROM File_Table ft INNER JOIN Folder_Table f ON f.folder_id = ft.folder_id UNION ALL SELECT f.parent_id FROM Folder_Table f INNER JOIN q ON q.folder_id = f.folder_id ) SELECT DISTINCT f.folder_id , f.parent_id , f.folder_name , f.is_active FROM q INNER JOIN Folder_Table f ON f.folder_id = q.folder_id WHERE f.is_active = 1 

Answer 2

This 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