Join on hierarchy query using the root element possible?

Hello,

I need to construct a query that will join permissions to a hierarchic table. The permission table stores user names and nodes in the hierarchy. When a user is granted permission on a node, all sub nodes should implicitly be granted permissions, recursively.

I would like to construct a list that shows all resulting permissions for each user, preferably in a single SQL statement.

Let me show you the case (simplified example).

• Script CREATE TABLE HIERARCHY ( ID NUMBER(10) NOT NULL
  , CONSTRAINT HIERARCHY_PK PRIMARY KEY ( ID )
  , PARENT NUMBER(10) NULL
  , NAME VARCHAR(10) NOT NULL
  );

CREATE TABLE PERMISSIONS
( HIERARCHY_ID NUMBER(10) NOT NULL
, USERNAME VARCHAR2(10) NOT NULL

);

ALTER TABLE PERMISSIONS ADD CONSTRAINT PERMISSIONS_HIERARCHY_FK  FOREIGN KEY (HIERARCHY_ID)
  REFERENCES HIERARCHY (ID) ;

INSERT INTO HIERARCHY VALUES (1, NULL, 'ROOT');
INSERT INTO HIERARCHY VALUES (2, 1, 'A');
INSERT INTO HIERARCHY VALUES (3, 2, 'AA');
INSERT INTO HIERARCHY VALUES (4, 1, 'B');

INSERT INTO PERMISSIONS VALUES (1, 'U1'); INSERT INTO PERMISSIONS VALUES (2, 'U2'); -- End script

I started with a CONNECT BY construction, but I may not use a JOIN in such a query. I would like to have all possible sub trees that result from any node a user has permissions on. This would look like: SELECT *
FROM HIERARCHY
CONNECT BY PRIOR ID = PARENT
START WITH ID IN (SELECT HIERARCHY_ID FROM PERMISSIONS) I get all the sub trees I need, but I cannot separate them anymore. This is, however, neccessay, since I want to add the username to the result.
What I would need is something like:
SELECT * FROM
(
SELECT ID, PARENT, PRIOR PARENT, NAME --, ROOT ID FROM HIERARCHY
CONNECT BY PRIOR ID = PARENT
) HIERARCHY
, PERMISSIONS;

--WHERE ROOT ID = HIERARCHY_ID
where the commented parts are not accepted by Oracle. ROOT ID would be something similar to PRIOR PARENT, only not referring to the previous level, but to the top level.

Does something like that exist? Or does anybody know another way to achieve this?

Thanks in advance,
Roel Schreurs
The Netherlands Received on Fri Oct 01 2004 - 05:37:38 CDT