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

Table Relationship

Sample data as well as desired results would be very useful for explaining your problem. — Gordon Linoff, Jun 13 '14 at 14:05
@GordonLinoff I have a link to the fiddle at the top of the question — SOfanatic, Jun 13 '14 at 14:06
It would also be helpful if your SQLFiddle column names matched the column names in your example query above. BaseTable in the SQLFiddle current has columns of UniqueId int, FKId int. I'm assuming UniqueId = BaseTableId and FKId = FileId? — BateTech, Jun 13 '14 at 15:57
@BateTech I've updated the Fiddle because I noticed that adding that `FK` column in the `BaseTable` was irrelevant to the question. — SOfanatic, Jun 13 '14 at 16:21
This question is confusing b/c you are using aliases (ParentId, ChildId) in place of actual column names in some cases. Can you add fully qualified column names (in parens beside the alias if you want to keep the aliases) in your question text that explains how these tables are supposed to relate to each other i.e. if you join Files.FileId = LinkingTable.FileId, then what does LinkingTable.RecursiveId for this matching row mean? Does LinkingTable.RecursiveId in this case match back to Files.RecursiveId? And the Files.FileId goes back to LinkingTable.FileId again? — BateTech, Jun 13 '14 at 17:00
@BateTech I included the alias next to the parent/child. But what you said about the relationships is right. I'm including a picture to help explain the relationships. — SOfanatic, Jun 13 '14 at 17:18

score 1 · Accepted Answer · answered Jun 13 '14 at 17:16

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

This matches the expected output from OP, but I do have a question. Should FileId 5 show up as a child for BaseTableID 1? Files.FileID 4 is the last row in OP expected output. The row for LinkingTable.FileID = 4 has LinkingTable.RecursiveId value of 3, which ties back to Files.ResursiveID = 3 and that row has Files.FileId value of 5? The Files.FileId 5 does not match back to a FileId in the LinkingTable, but according to your diagram in the updated question, it appears that it should still show as a child file for BaseTableId 1. — BateTech, Jun 13 '14 at 17:53
FileId 5 shouldn't show up as a child for BaseTableId 1 because it is not in the LinkingTable. If a file is in the LinkingTable it means that it's "been used" somewhere so I'm just looking for all files that have "been used". In conclusion FileId 5 IS a child for BaseTableId 1, but it should not be part of the result because it hasn't "been used". — SOfanatic, Jun 13 '14 at 17:59

Recursive CTE with alternating tables

1 Answers1