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
