Can SQL Server Hierarchy type method IsDescendantOf accept multiple input values?

I am using the HierarchyId data type for the storage of locations. A user may be limited by location ( LocationId ). If the user has more than 1 location limit the IsDescendantOf method on the HierarchyId data type has to be invoked again with an OR .

Example(filter Employees by LocationId 5 and 6):

SELECT * FROM Employee
INNER JOIN Location ON Employee.LocationId = Location.LocationId
WHERE Location.Node.IsDescendantOf((SELECT TOP 1 Node
    FROM Location 
    WHERE LocationId = 5)) = 1
OR 
Location.Node.IsDescendantOf((SELECT TOP 1 Node
    FROM Location 
    WHERE LocationId=6)) = 1`

This works fine for 2 LocationId filters but what if this grows and a person has say 10 filters. Can IsDescendantOf work like the sql IN clause?

Tables used:

CREATE TABLE Location (
LocationId int NOT NULL PRIMARY KEY IDENTITY(1,1),
Name       nvarchar(100) NOT NULL,
[Node]  hierarchyid    NOT NULL,
[ParentNode]  AS ([Node].[GetAncestor]((1))) PERSISTED,
[Level]  AS ([Node].[GetLevel]()) PERSISTED,
);
CREATE TABLE [dbo].[Employee] (
[EmployeeId] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[LocationId] [int] NULL,
[Name] [nvarchar](50) NULL
) ;

Note: I addes the second solution (point 6).

You may use a table variable to store all searched locations (ex. DECLARE @SearchedAncestorLocation TABLE(LocationId INT PRIMARY KEY) ).
You have to find HIERARCHYID's nodes for every location ID from @SearchedAncestorLocation .
You have to do an INNER JOIN with employee's location using this filter: employee_location.Node.IsDescendantOf(searched_location.Node) = 1 .
I think you should add an UNIQUE(Node) constraint to Location table to prevent duplicated locations (duplicated nodes).
First solution: demo here .

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/'),
            ( 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/'), -- <-- Second employee @ BA-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

Results:

EmployeeId LocationId  Name          EmpLocationName EmpLocationHID SearchedLocationId SearchedLocationName SearchedLocationHID
---------- ----------- ------------- --------------- -------------- ------------------ -------------------- -------------------
1          3           Ion Ionescu   AA-1            /1/1/1/        1                  A                    /1/
1          3           Ion Ionescu   AA-1            /1/1/1/        2                  AA                   /1/1/
1          3           Ion Ionescu   AA-1            /1/1/1/        3                  AA-1                 /1/1/1/
2          11          Geo Georgescu BA-1            /2/1/1/        9                  B                    /2/
2          11          Geo Georgescu BA-1            /2/1/1/        10                 BA                   /2/1/

Results if you uncomment the last line ( AND sl.Node <> el.Node ):

EmployeeId LocationId  Name          EmpLocationName EmpLocationHID SearchedLocationId SearchedLocationName SearchedLocationHID
---------- ----------- ------------- --------------- -------------- ------------------ -------------------- -------------------
1          3           Ion Ionescu   AA-1            /1/1/1/        1                  A                    /1/
1          3           Ion Ionescu   AA-1            /1/1/1/        2                  AA                   /1/1/
2          11          Geo Georgescu BA-1            /2/1/1/        9                  B                    /2/
2          11          Geo Georgescu BA-1            /2/1/1/        10                 BA                   /2/1/

Second solution.

SELECT  e.EmployeeId,
            e.LocationId,
            e.Name
    FROM    @Employee e
    INNER JOIN  @Location el ON e.LocationId = el.LocationId
    WHERE EXISTS 
    (
            SELECT  *
            FROM    @SearchedAncestorLocation s
            INNER JOIN @Location sl ON s.LocationId = sl.LocationId 
            WHERE   el.Node.IsDescendantOf(sl.Node) = 1
            --AND       el.Node <> sl.Node 
    );

Can SQL Server Hierarchy type method IsDescendantOf accept multiple input values?

1条答案

相关问题

热门标签

最新问答