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 1 and use that to look up a FileId in the Files table, then use that FileId to look for a ChildId 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