Q&A for meta-discussion of the Stack Exchange family of Q&A websites
DECLARE @Location TABLE( LocationId int NOT NULL PRIMARY KEY, Name nvarchar(100) NOT NULL, [Node] hierarchyid NOT NULL, UNIQUE ([Node]) ); DECLARE @Employee TABLE ( [EmployeeId] [int] PRIMARY KEY, [LocationId] [int] NULL, [Name] [nvarchar](50) NULL ); INSERT @Location(LocationId, Name, [Node]) VALUES ( 1, N'A', '/1/'), ( 2, N'AA', '/1/1/'), ( 3, N'AA-1', '/1/1/1/'), -- <-- First employee @ AA-1 ( 4, N'AA-2', '/1/1/2/'), -- <-- Second employee @ AA-2 ( 5, N'AA-3', '/1/1/3/'), ( 6, N'AB', '/1/2/'), ( 7, N'AA-1', '/1/2/1/'), ( 8, N'AB-2', '/1/2/2/'), ( 9, N'B', '/2/'), (10, N'BA', '/2/1/'), (11, N'BA-1', '/2/1/1/'), (12, N'BA-2', '/2/1/2/'), (13, N'BA-3', '/2/1/3/'), (14, N'BB', '/2/2/'), (15, N'BB-1', '/2/2/1/'); INSERT @Employee(EmployeeId, [Name], LocationId) VALUES (1, N'Ion Ionescu', 3), -- AA-1 (2, N'Geo Georgescu', 11); -- BA-1 DECLARE @SearchedAncestorLocation TABLE(LocationId INT PRIMARY KEY); INSERT @SearchedAncestorLocation VALUES (1), --A (2), --AA (3), --AA-1 (9), --B (10), --BA (14); --BB SELECT e.*, el.Name AS EmpLocationName, el.Node.ToString() AS EmpLocationHID, s.LocationId AS SearchedLocationId, sl.Name AS SearchedLocationName, sl.Node.ToString() AS SearchedLocationHID FROM @Employee e INNER JOIN @Location el ON e.LocationId = el.LocationId INNER JOIN @Location sl ON el.Node.IsDescendantOf(sl.Node) = 1 INNER JOIN @SearchedAncestorLocation s ON sl.LocationId = s.LocationId --AND sl.Node <> el.Node