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!