CODEWITHSUNDEEP
sqlsqlitecommon-table-expression
Fgbruna
Fgbruna

Reputation: 127

Recursive CTE including unions inside anchor and recursive expression

So i have three tables with the following schema,

Users(id, name)

Colleagues(id1, id2)

Friends(id1, id2)

And i need to write a query that returns every pair of id's so that id_2 can be reached from id_1 using an arbitrary number of connections between colleagues and friends. I worked out a query that gives me every connection using either Colleagues or Friends, but not both. This is is what i came up with trying to use both tables in the same CTE:

WITH RECURSIVE Reachable (id_1, id_2)
AS (
SELECT
    *
FROM (
    SELECT
        id,
        FRIENDS.id2
    FROM
        USERS,
        FRIENDS
    WHERE
        FRIENDS.id1 = USERS.id
    UNION
    SELECT
        id,
        COLLEAGUES.id2
    FROM
        USERS,
        COLLEAGUES
    WHERE
        COLLEAGUES.id1 = USERS.id)
UNION
SELECT
    *
FROM (
    SELECT
        REACHABLE.id_1,
        FRIENDS.id2
    FROM
        REACHABLE,
        FRIENDS
    WHERE
        REACHABLE.id_2 = FRIENDS.id1
    UNION
    SELECT
        REACHABLE.id_1,
        COLLEAGUES.id2
    FROM
        REACHABLE,
        COLLEAGUES
    WHERE
        REACHABLE.id_2 = COLLEAGUES.id1));

But i'm getting this error:

Error: near line 1: recursive reference in a subquery: Reachable

Does that mean i can't/shouldn't use subqueries in a recursive call in general? is it even possible to perform this query inside the same CTE? if so, how could i do it? Thanks in advance!

Upvotes: 0

Views: 410

Answers (1)

CL.
CL.

Reputation: 180080

The reference to the recursive CTE must not be in a subquery, and the two parts separated with UNION (ALL) must be a the top level of the WITH.

If there is no difference between friends and colleagues for this query, just merge the two tables before doing the recursive CTE:

WITH RECURSIVE
Connections AS (
  SELECT id_1, id_2 FROM Colleagues
  UNION ALL 
  SELECT id_1, id_2 FROM Friends
),
Reachable(id_1, id_2) AS (
  SELECT ...
  FROM Users, Connections
  ...
  UNION
  ...
)
SELECT * FROM Reachable;

Upvotes: 1

Related Questions