So i have three tables with the following schema,
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!