CODEWITHSUNDEEP
sqlsql-servert-sqlhierarchyid
SQL_Guy
SQL_Guy

Reputation: 353

Find value contained in the HierarchyId at any level

I need to find a particular value contained in the SQL Server HierarchyId column. The value can occur at any level. Here is a sample code to illustrate the issue:

CREATE TABLE mytable
(
    Id INT NOT NULL PRIMARY KEY,
    TeamName VARCHAR(20) NOT NULL, 
    MyHierarchyId HIERARCHYID NOT NULL
);

INSERT INTO mytable(Id, TeamName, MyHierarchyId) 
VALUES (1, 'Corporate','/1/');

INSERT INTO mytable(Id, TeamName, MyHierarchyId) 
VALUES (2, 'Group A','/1/2/');

INSERT INTO mytable(Id, TeamName, MyHierarchyId) 
VALUES (3, 'Team X','/1/2/3/');

INSERT INTO mytable(Id, TeamName, MyHierarchyId) 
VALUES (4, 'Group B','/1/4/');

INSERT INTO mytable(Id, TeamName, MyHierarchyId) 
VALUES (5, 'Team Y','/1/4/5/');

INSERT INTO mytable(Id, TeamName, MyHierarchyId) 
VALUES (6, 'Team Z','/1/4/6/');

Now I would like to find all the records, which are associated with the Id = 4. This means records 4, 5 and 6. I could use a brute force methods like this:

SELECT [M].[Id],
       [M].[TeamName],
       [M].[MyHierarchyId],
       [M].[MyHierarchyId].ToString() AS Lineage
FROM [dbo].[mytable] AS [M]
WHERE [M].[MyHierarchyId].ToString() LIKE '%4%'

But I suspect this will be very inefficient. Once again, the problem is that the level of the node I am searching for is not known in advance.

Thank you for any recommendations.

Upvotes: 1

Views: 467

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82020

You can use IsDescendantOf()

Select * 
 from mytable
 Where MyHierarchyID.IsDescendantOf( (select MyHierarchyID from mytable where id=4)  ) = 1

Results

Id  TeamName    MyHierarchyId
4   Group B     0x5C20
5   Team Y      0x5C3180
6   Team Z      0x5C3280

Upvotes: 5

Related Questions