Hello.
I have 2 tables:
CREATE TABLE "group"
(
id serial NOT NULL
"name" character varying(23) NOT NULL
id_user integer NOT NULL DEFAULT 0,
parent integer DEFAULT 0,
CONSTRAINT group_user_fkey FOREIGN KEY (id_user) REFERENCES "user"
(id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
)
and
CREATE TABLE "user"
(
id serial NOT NULL
"login" character varying(12) NOT NULL
parent integer DEFAULT 0
)
Can I get a tree of records in a single query, and their two tables
using "WITH RECURSIVE".
tree one table I made, but how to combine these queries do not understand.
WITH RECURSIVE gg(id,parent,level,path,cycle) AS (
SELECT id,parent,0,ARRAY[id],false FROM web."group" WHERE id=899
UNION ALL
SELECT g.id,g.parent,level + 1,path||g.id,g.id=ANY(path) FROM
web."group" as g,gg WHERE g.parent=gg.id AND NOT cycle
)
SELECT u.id,u.name,path FROM web."group" as u, gg WHERE gg.id=u.id;
WITH RECURSIVE uu(id,parent,level,path,cycle) AS (
SELECT id,id_user,0,ARRAY[id],false FROM web."user" WHERE id=71
UNION ALL
SELECT u.id,u.id_user,level + 1,path||u.id,u.id=ANY(path) FROM
web."user" as u,uu WHERE u.id_user=uu.id AND NOT cycle
)
SELECT u.id,u.login,path FROM web."user" as u, uu WHERE uu.id=u.id;
Could you please help me.