CTE with subqueries in anchor and recursive call

1

It turns out that I have these three tables,

Users(id, name)

Colleagues(id1, id2)

Friends(id1, id2)

I need to write a query that gives me pairs of ids so that one can "get" from id1 to id2 with an arbitrary number of colleagues and / or friends. That is, id1 is connected to id2 through a certain amount of colleagues or friends. I can make a query that all achievable friends or achievable colleagues give me, but I have not managed to use both to build the connections. I assumed that I could do it with subqueries as follows:

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 this error is generated:

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

Does it mean that I can not use recursive calls in subqueries in general or is it another problem? Can I do the consultation I need using only a CTE? Thanks in advance!

    
asked by Fgbruna 29.06.2018 в 22:48
source

0 answers