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.

CTE is a new concept for me. I have been trying it but not successful. — Troy Mitchel
– Troy Mitchel, Commented Feb 9, 2012 at 15:08

MRB · Accepted Answer · 2012-02-09 15:19:23Z

7

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

answered Feb 9, 2012 at 15:19

MRB

3,9461 gold badge16 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lieven Keersmaekers Over a year ago

+1, all I would change is formatting and using the names OP uses.

Lieven Keersmaekers · Accepted Answer · 2012-02-09 19:12:12Z

6

A slightly different way including all columns just 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

edited Feb 9, 2012 at 19:12

answered Feb 9, 2012 at 15:45

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

5 Comments

Troy Mitchel Over a year ago

Thank you, this gave the exact results I was looking for in the sample of my original question.

Troy Mitchel Over a year ago

on another note, i also have an is_active column in the foler table either set to 0 or 1. 1 = active. So i'd like to only show those where is_active = 1. I added it to the query in a few different ways but cannot get it to work.

Lieven Keersmaekers Over a year ago

What would you expect if Folder 1 is active, folder 2 inactive and folder 4 active?

Troy Mitchel Over a year ago

If folder2 in inactive then all of its children would be inactive too.

Lieven Keersmaekers Over a year ago

In essence, all you need to do is add a where clause to the final select statement. I have altered my answer to reflect this.

Collectives™ on Stack Overflow

SQL Query Recursion for Parent Child relationship

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related