0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for community managers, administrators, and moderators

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

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

fork query

edited may 21 anonymous

created may 5 12 jon.doe7881