Combining a recursive CTE with another query

I have a table of locations each of which can have a parent location

LocationId | ParentLocationId
-----------------------------
     1              null
     2                1
     3                2
     4                2

I managed to create a recursive CTE which gives me parent location id (plus the original location id) for any given location id

WITH       GetLocationParents AS
  (
    select    [LocationId],    [ParentLocationId]   from    Locations
    where     LocationId = 3
    UNION ALL 
    select    i.[LocationId],    i.[ParentLocationId]
    from        Locations i 
    join  GetLocationParents cte on cte.ParentLocationId = i.LocationId
  )
SELECT  [ParentLocationId] FROM GetLocationParents
WHERE   [ParentLocationId] is not NULL;

e.g. where LocationId = 3 would return:

ParentLocationId
----------------
       3
       2
       1

In another table I have a query which will return LocationId as one of the fields:

select exi.PersonId, exi.LocationId from Persons e
left join PersonHasLocations exi on e.PersonId = exi.PersonId
left join Locations i on exi.LocationId = i.LocationId

Which with a where clause would return something like:

PersonId | LocationId
---------------------
    100          3

I’m trying to combine these queries to get the result:

PersonId | LocationId
---------------------
   100        3
   100        2
   100        1

I’m trying the following but it’s still only returning the first row:

WITH
    GetLocationParents AS
        (select    [LocationId],    [ParentLocationId]   from    Locations
        --where LocationId = 3
        UNION ALL 
        select    i.[LocationId],    i.[ParentLocationId]
        from    Locations i    inner join GetLocationParents cte 
        on cte.ParentLocationId = i.LocationId),
    GetPersons AS
        (select exi.PersonId, exi.LocationID from Persons e
        left join PersonHasLocations exi on e.PersonID = exi.PersonId
        left join Locations i on exi.LocationId = i.LocationID)
SELECT * FROM GetLocationParents gip
INNER JOIN GetPersons ge on ge.LocationId = gip.LocationID
WHERE ge.PersonId = 100

Is it possible to merge a recursive query with a normal query like this?

Answer

I guess you have a small bug in your cte. I would suggest to change the query as follows:

DECLARE @t TABLE (
  LocationId int,
  ParentLocationId int
)

INSERT INTO @t VALUES
    (1, NULL)
   ,(2, 1)
   ,(3, 2)
   ,(4, 2)

;WITH       GetLocationParents AS
  (
    select    [LocationId] AS k, [LocationId],    [ParentLocationId]   from    @t
    UNION ALL 
    select    k, i.[LocationId],    i.[ParentLocationId]
    from        GetLocationParents cte
    join @t i   on cte.ParentLocationId = i.LocationId
  )
SELECT  *
 FROM GetLocationParents
 WHERE k = 3

With this you receive a list with the value you filter on in the first column and all depending “levels” above this in the second column. This can then be used in order to join to your second table.

Keep in mind that – depending on your number of levels – you will have to take care of MAX RECUSRSION.