WITH RECURSIVE from 2 or more tables.

[Капралов Александр <alnkapa@xxxxxxxxx>]

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.


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general