T-SQL Tree Search Select from set of nodes if they are under a parent

Question

T-SQL Tree Search

Select from set of nodes if they are under a parent

I have a very large tree in a MSSQL Db (80000+) records. My client has requested a quick search of the tree via a text LIKE command. The LIKE command returns < 500 records.

Is there some recursive command that will check the tree of each quickly to see if they are under a particular node?

Edit: I thought it was fairly clear however....

I am on SQL Server 2005.

Table Schema
 - (pK) Id
 - (fK) ParentId
 - FirstName
 - LastName

I have recursive calls that are able to go down several levels quickly. however to do the Name search I would have to poll the entire tree which can be several hundred levels deep and is not an option. I was hoping for help designing a query so I can search the entire table first for the name match and filter the records that are not part of the tree in question.

Answer 1

You can do this with a recursive CTE -- do you need a code example?

Like this (this code was tested)

WITH recurseTree AS
(
   SELECT * 
   FROM tableName
   WHERE Id = @parentID
   UNION ALL
   SELECT c.*
   FROM tableName c
   JOIN recurseTree p ON c.parentID = p.id
)
SELECT * 
FROM recurseTree

Note : More recient versions (2008+) give you a special datatype (hierarchyid) for doing fast tree traversals. This would probably be the best way to go, anything else is not going to be as fast. Upgrade!!

Answer 2

This should help you:

;WITH CTE AS
(
    SELECT Id, ParentId, FirstName, LastName
    FROM YourTable
    UNION ALL
    SELECT B.Id, B.ParentId, B.FirstName, B.LastName
    FROM CTE A 
    INNER JOIN YourTable B
    ON A.ParentId = B.Id
)
SELECT *
FROM CTE
WHERE FirstName LIKE '%something%'
OPTION(MAXRECURSION 0)