Find contact in location tree

Question

I have the following table:

Location:

ID Name        Depth ParentID    HierachyPath       ContactID
1  US            0     NULL           \                25
2  Florida       1     1             \001             NULL
3  Miami         2     2             \001\001         NULL
4  Dade County   3     3             \001\001\001     NULL
5  Orlando       2     2             \001\002         15
6  County1       3     3             \001\002\001     12

I will be pased a location ID such as 4. I will get multiple location ID's passed to me at once, so I may need to find the contact for 1 location or 1000 locations at once. If there is not a contact at location 4 I need to travel up the HierachyPath until I find a contact. Any ideas on how to do this?

Answer 1

I added the second solution (multiple IDs)

First solution

CREATE TABLE CocoJamboTable (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    HierachyPath HIERARCHYID NOT NULL,
    ContactID INT NULL -- REFERENCES .... (ContactID)
);
INSERT INTO dbo.CocoJamboTable (ID, Name, HierachyPath, ContactID)
VALUES 
 (1, 'US              ', '/',           25)
,(2, 'Florida         ', '/1/',         NULL)
,(3, 'Miami           ', '/1/1/',       NULL)
,(4, 'Dade County     ', '/1/1/1/',     NULL)
,(5, 'Orlando         ', '/1/2/',       15)
,(6, 'County1         ', '/1/2/1/',     12);

CREATE UNIQUE INDEX IUN_CocoJamboTable_HierachyPath_#_ContactID
ON dbo.CocoJamboTable(HierachyPath) 
INCLUDE (ContactID);
GO

DECLARE @ID INT=4;

DECLARE @ContactID INT = 
(
SELECT d.ContactID
FROM  (
    SELECT  c.ContactID,
            ROW_NUMBER() OVER(
                ORDER BY CASE WHEN c.ContactID IS NOT NULL THEN 1 ELSE 2 END, -- First: NOT NULL ContacIDs 
                b.Number ASC    
            ) AS RowNum
    FROM    dbo.CocoJamboTable a
    JOIN    (VALUES (0),(1),(2),(3),(5),(6),(7),(8),(9),(10)) b(Number) ON a.HierachyPath.GetLevel() >= b.Number
    JOIN    dbo.CocoJamboTable c WITH(FORCESEEK) ON c.HierachyPath=a.HierachyPath.GetAncestor(b.Number)
    -- FORCESEEK: For some reason, the index (IUN_CocoJamboTable_HierachyPath_#_ContactID) is not used by default
    WHERE   a.ID=@ID
) d
WHERE d.RowNum=1
);

SELECT  @ContactID AS [@ContactID];

SELECT  a.ID, 
        c.ContactID,
        a.HierachyPath.ToString() AS HierachyPathAsString, 
        a.HierachyPath.GetLevel() AS HierachyPathGetLevel,
        b.Number,
        a.HierachyPath.GetAncestor(b.Number).ToString() AS HierachyPathGetAncestor_n_Number
FROM    dbo.CocoJamboTable a
JOIN    (VALUES (0),(1),(2),(3),(5),(6),(7),(8),(9),(10)) b(Number) ON a.HierachyPath.GetLevel() >= b.Number
JOIN    dbo.CocoJamboTable c ON c.HierachyPath=a.HierachyPath.GetAncestor(b.Number)
WHERE   a.ID=@ID
ORDER BY b.Number DESC

Results:

@ContactID
-----------
25


ID ContactID HierachyPathAsString HierachyPathGetLevel Number      HierachyPathGetAncestor_n_Number
-- --------- -------------------- -------------------- ----------- --------------------------------
4  25        /1/1/1/              3                    3           /
4  NULL      /1/1/1/              3                    2           /1/
4  NULL      /1/1/1/              3                    1           /1/1/

Second solution (multiple IDs)

DECLARE @IDs TABLE (ID INT PRIMARY KEY);
INSERT  @IDs 
VALUES (4), (5), (6);

SELECT  *
FROM    @IDs v
OUTER APPLY
(
    SELECT d.ContactID
    FROM  (
        SELECT  c.ContactID,
                ROW_NUMBER() OVER(
                    ORDER BY CASE WHEN c.ContactID IS NOT NULL THEN 1 ELSE 2 END, -- First: NOT NULL ContacIDs 
                    b.Number ASC    
                ) AS RowNum
        FROM    dbo.CocoJamboTable a
        JOIN    (VALUES (0),(1),(2),(3),(5),(6),(7),(8),(9),(10)) b(Number) ON a.HierachyPath.GetLevel() >= b.Number
        JOIN    dbo.CocoJamboTable c WITH(FORCESEEK) ON c.HierachyPath=a.HierachyPath.GetAncestor(b.Number)
        -- FORCESEEK: For some reason, the index (IUN_CocoJamboTable_HierachyPath_#_ContactID) is not used by default
        WHERE   a.ID=v.ID
    ) d
    WHERE d.RowNum=1
) oa;

Answer 2

You could use a recursive common-table expression to walk up until you find a parent with a contact. For example:

; with  CTE as
        (
        select  ID
        ,       ContactID
        ,       ParentID
        ,       ID BaseID
        ,       1 as Level
        from    Location
        where   ID in (4,5)
        union all
        select  parent.ID
        ,       parent.ContactID
        ,       parent.ParentID
        ,       child.BaseID
        ,       child.Level + 1
        from    Location parent
        join    CTE child
        on      parent.ID = child.ParentID
        -- Stop when we have a contact          
        where   child.ContactID is null
        )
select  CTE.BaseID as ID
,       CTE.ContactID
from    CTE
where   ContactID is not null;

Example at SQL Fiddle.

Answer 3

I am not sure if your data is correct as Florida has no ParentId which exists in the given list of data. I think following query will give you the results you need. Using CTE

SQL-FIDDLE-DEMO

declare @myId int = 4

;with cte as (
  select id,pid, cid
  from L where  id = @myId
  union all
  select L.id,L.pid,L.cid
  from cte join L on cte.pid = L.id 
  where cte.cid is null
)
select top (1) cid from cte
order by pid

This works only if you pass a single Id, not for your updated question. Little change can make it work for that as well. @Andomar has addressed that above.