SQL Server statement to get children using parent id - refactor

Question

I have a table content_folder_tbl that has a list of all folders and sub folders defined with folder_id and parent_id

I want to get all the folders id once I define the root folder_id.

As a workaround I came up with this query :

 select * 
 from content_folder_tbl 
 where folder_id in (select folder_id 
                     from content_folder_tbl 
                     where parent_id = 73 
                     UNION
                     Select folder_id 
                     from content_folder_tbl 
                     where parent_id in (select folder_id 
                                         from content_folder_tbl 
                                         where parent_id = 73)
                     Union
                     Select folder_id 
                     from content_folder_tbl 
                     where parent_id in (Select folder_id 
                                         from content_folder_tbl 
                                         where parent_id in (select folder_id
                                                             from content_folder_tbl 
                                                             where parent_id = 73))
  )

It returns exactly what I want, but it's redundant and works only if I have 3 levels folders.

How to rewrite this query to return values for any level (looks like I need a recursive approach)?

73 is the root folder_id in this case

Example of expected result:

Possible duplicate of [Tree structure data query in SQL Server](http://stackoverflow.com/questions/10369634/tree-structure-data-query-in-sql-server) — Sean Lange, Nov 25 '15 at 15:25

Devart · Accepted Answer · 2015-11-25T15:41:59.830

3

try this one -

;WITH cte AS
(
    SELECT *
    FROM dbo.content_folder_tbl
    WHERE folder_id = 73

    UNION ALL

    SELECT t2.*
    FROM cte t1
    JOIN dbo.content_folder_tbl t2 ON t1.folder_id = t2.parent_id
)
SELECT *
FROM cte
OPTION (MAXRECURSION 0)

edited Nov 25 '15 at 15:41

answered Nov 25 '15 at 15:23

Devart

119,203
23
166
186

returns more values than expected, but looks like a good base for me – IndieTech Solutions Nov 25 '15 at 15:26
please provide a small example with expected result – Devart Nov 25 '15 at 15:30
please check a query. sorry :) don't have a crystal ball – Devart Nov 25 '15 at 15:42
i posted the right expected result now. sorry about that – IndieTech Solutions Nov 25 '15 at 15:42
Yes that was the right one . answer accepted. the only missing thing is that it includes the root folder_id, but i can fix that – IndieTech Solutions Nov 25 '15 at 15:48
1

great. have a nice day :) – Devart Nov 25 '15 at 15:49

SQL Server statement to get children using parent id - refactor

1 Answers1