Is there a way to recursively join 2 different SQL tables in a select query?

Question

I have the following tables in a database:

LIST: which contains metadata of some list.
LISTITEM: which is a single list entry, linking a thing as ObjectID, and links back to a LIST by referencing the ListID.
OBJECT: An actual thing that is to be included in these lists. These are what we commonly reference in the LISTITEM to be listed.

However, in addition to OBJECT, another LIST can also be referenced in a LISTITEM, creating a hierarchy of nested lists within each other.

My goal is to query all FOOs that contain BAR Objects that are nested within a hierarchy of linked LISTs.

My sample code so far:

SELECT F.ID, F.Name
FROM Foo F
LEFT JOIN List L ON L.ID = F.ListID
LEFT JOIN ListItem LI ON LI.ListID = L.ID
LEFT JOIN List L2 ON L2.ID = LI.ChildListID
LEFT JOIN ListItem LI2 ON LI2.ListID = L2.ID
LEFT JOIN Object O ON O.ID in (LI.ObjectID, LI2.ObjectID)
WHERE O.Name like 'Bar'

However, this only will search 2 levels deep in the hierarchy and I would have to configure my WHERE statements to compare every level of Object O I create.

How can I fix up my query to be able to search within a hierarchy of N levels?

Does this answer your question? [CTE Recursion to get tree hierarchy](https://stackoverflow.com/questions/18106947/cte-recursion-to-get-tree-hierarchy) — devlin carnate, Apr 02 '21 at 18:11
The CTE examples I've seen and been linked show how to recursively join a single table to itself. In my scenario, there's a 2 table relationship that I need to recursively join. — Vicros, Apr 02 '21 at 18:50
It would make answering a lot easier if you provided a https://stackoverflow.com/help/minimal-reproducible-example — Koen Lostrie, Apr 02 '21 at 18:52
Please edit your question and supply test data for each table you're using. Thanks. — Bob Jarvis - Слава Україні, Apr 02 '21 at 19:25

kfinity · Answer 1 · 2021-04-05T15:24:12.503

You can do recursive CTEs in Oracle, too. In this case, I think it's a bit more clear than connect by.

So to build a recursive CTE, start with your base query that works for the 1st level of the hierarchy (in your case, directly connected to a Foo).

select L.ID, LI.OBJECT_ID, LI.ChildListID
from List L
join ListItem LI ON LI.ListID = L.ID
where L.ID = 12345;

Then you put it in a CTE, and UNION a recursive layer on to it, which queries the CTE itself.

with list_hier  (id, object_id, childlistid, lvl) as (
    select L.ID, LI.OBJECT_ID, LI.ChildListID, 1 as lvl
        from List L
        join ListItem LI ON LI.ListID = L.ID
    union all
    select H.ID, -- important to keep the root list id for join to Foo
            LI.OBJECT_ID, LI.ChildListID, H.lvl + 1 as lvl
        from List L
        join ListItem LI ON LI.ListID = L.ID
        join list_hier H on H.ChildListID = L.ID)
select * from list_hier
where ID = 12345;

Now it selects the Objects on List #12345 AND all of its descendents.

In this case, we don't actually need the List table for the recursive part of the query, since none of its columns are being selected, and its ID column is used for both join conditions. So I'll remove it! If you find this confusing, or need other columns from List, feel free to stick with the CTE above.

Now that you have a working recursive CTE, just join it as you would any other table or view.

with list_hier (id, object_id, childlistid) as (
    select L.ID, LI.OBJECT_ID, LI.ChildListID
        from List L
        join ListItem LI ON LI.ListID = L.ID
    union all
    select H.ID, 
            LI.OBJECT_ID, LI.ChildListID
        from list_hier H
        join ListItem LI ON LI.ListID = H.ChildListID)
SELECT F.ID, F.Name
FROM Foo F
JOIN list_hier H
    ON H.ID = F.ListID
JOIN Object O 
    ON O.ID = H.OBJECT_ID
WHERE O.Name like 'Bar'

I changed the joins to inner joins here for clarity, since the WHERE clause excludes any outer-joined rows with null values.

This looks fantastic. Trying it out but I'm getting an error that the "recursive WITH clause must have column alias list". Attempting to assign alias to each column but I'm still getting the error. — Vicros, Apr 02 '21 at 22:31
@Vicros - whoops, forgot that. It's a confusing error, the column alias list needs to be after the CTE alias name (like you're creating a table or a view). Edited my answer to add that. — kfinity, Apr 05 '21 at 15:25

Is there a way to recursively join 2 different SQL tables in a select query?

1 Answers1