[Solved] Find contact in location tree

by


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.

0

solved Find contact in location tree