Logics

ID         Name         ParentID
-------------------------------------
1         Computer          0
2         Software          1
3         Hardware          1
4         Windows           2
5         Games             0
6         Windows           5
7         Linux             5
8         3D                6
9         DirectX           8

I want to search on this table for word 'Windows' and I want results as below:

ID         Name         ParentID
-------------------------------------
1         Computer          0          <== Grandparent of 4
2         Software          1          <== Parent of 4
4         Windows           2          <== 4
5         Games             0          <== Parent of 6
6         Windows           5          <== 6

I mean all parents which has a relation with search word should be kept and the rest should be removed from records

 

 

 

with C as 
(
  select T.ID, T.Name, T.ParentID
  from @T as T
  where Name = 'Windows'
  union all
  select T.ID, T.Name, T.ParentID
  from YourTable as T
    inner join C
      on T.ID = C.ParentID
)
select ID, Name, ParentID
from C
order by ID;

NodeId    NodeName    ParentId
------------------------------
1         Node1       0
2         Node2       0
3         Node3       1
4         Node4       1
5         Node5       3
6         Node6       5
7         Node7       2

with [CTE] as (
    select * from [TheTable] c where c.[ParentId] = 1
    union all
    select * from [CTE] p, [TheTable] c where c.[ParentId] = p.[NodeId]
)
select * from [CTE]

[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

;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