Recursive CTE with alternating tables

Question

I've created a SQL fiddle here.

Basically, I have 3 tables BaseTable, Files, and a LinkingTable.

The Files table has 3 columns: PK, BaseTableId, RecursiveId (ChildId). What I want to do is find all the children given a BaseTableId (i.e., ParentId). The tricky part is that the way the children are found works like this:

Take ParentId(BaseTable.BaseTableId) 1 and use that to look up a FileId in the Files table, then use that FileId to look for a ChildId(LinkingTable.RecursiveId) in the LinkingTable, if that record exists then use the RecursiveId in the LinkingTable to look for the next FileId in the Files table and so on.

This is my CTE so far:

with CTE as
(
    select lt.FileId, lt.RecursiveId, 0 as [level],
        bt.BaseTableId
    from BaseTable bt
    join Files f
    on bt.BaseTableId = f.BaseTableId
    join LinkingTable lt
    on f.FileId = lt.FileId
    where bt.BaseTableId = @Id
    UNION ALL
    select rlt.FileId, rlt.RecursiveId, [level] + 1 as [level],
        CTE.BaseTableId
    from CTE --??? and this is where I get lost
...
)

A correct output for BaseTableId = 1, should be:

FileId|RecursiveId|level|BaseTableId
  1        1         0        1
  3        2         1        1
  4        3         2        1

Table Relationship

Answer 1

Here is a recursive example that I believe meets your criteria. I added a ParentId to the result set, which will be NULL for the root/base file, since it does not have a parent.

declare @BaseTableId int;
set @BaseTableId  = 1;

; WITH cteRecursive as (
    --anchor/root parent file
    SELECT null as ParentFileId
        , f.FileId as ChildFileID
        , lt.RecursiveId 
        , 0 as [level]
        , bt.BaseTableId
    FROM BaseTable bt
        INNER JOIN Files f
            on bt.BaseTableId = f.BaseTableId
        INNER JOIN LinkingTable lt
            on f.FileId = lt.FileId
    WHERE bt.BaseTableId = @BaseTableId 

    UNION ALL 

    SELECT cte.ChildFileID as ParentFileID 
        , f.FileId as ChildFileID
        , lt.RecursiveId
        , cte.level + 1 as [level]
        , cte.BaseTableId
    FROM cteRecursive cte
        INNER JOIN Files f on cte.RecursiveId = f.RecursiveId
        INNER JOIN LinkingTable lt ON lt.FileId = f.FileId
)
SELECT * 
FROM cteRecursive
;

Results for @BaseTableID = 1:

ParentFileId ChildFileID RecursiveId level       BaseTableId
------------ ----------- ----------- ----------- -----------
NULL         1           1           0           1
1            3           2           1           1
3            4           3           2           1

Results for @BaseTableID = 2:

ParentFileId ChildFileID RecursiveId level       BaseTableId
------------ ----------- ----------- ----------- -----------
NULL         2           1           0           2
NULL         2           4           0           2
2            6           5           1           2
6            7           6           2           2
2            3           2           1           2
3            4           3           2           2