CODEWITHSUNDEEP
sqlsql-servert-sql
Mertez
Mertez

Reputation: 1131

SQL Query for search in a Parent Child relationship and retrieve all nodes and all their parents

I have db table with parent child relationship like:

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

Upvotes: 1

Views: 2768

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You can use a Recursive CTE

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;

SE-Data

Upvotes: 2

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28738

Use the HierarchyId data type and then use the IsDescendant of method

SELECT * FROM Table
WHERE @searchId.IsDescendantOf(ID) = 1

This allows you to perform arbitrary recursion and/or looping. It's fast and straightforward.

Upvotes: 1

Related Questions