Oracle hierarchical query

Question

Using Oracle 10g. I have two tables:

User Parent ------------- 1 (null) 2 1 3 1 4 3 Permission User_ID ------------------- A 1 B 3 

Values ​​in the permissions table are inherited before children. I would like to write one query that could return me something like this:

 User Permission ------------------ 1 A 2 A 3 A 3 A 3 B 4 A 4 B 

Is it possible to formulate such a query using 10g connect .. syntax to get lines from previous levels?

Answer 1

you can achieve the desired result with connect by (and with the CONNECT_BY_ROOT function, which returns the value of the column of the root node):

 SQL> WITH users AS ( 2 SELECT 1 user_id, (null) PARENT FROM dual 3 UNION ALL SELECT 2, 1 FROM dual 4 UNION ALL SELECT 3, 1 FROM dual 5 UNION ALL SELECT 4, 3 FROM dual 6 ), permissions AS ( 7 SELECT 'A' permission, 1 user_id FROM dual 8 UNION ALL SELECT 'B', 3 FROM dual 9 ) 10 SELECT lpad('*', 2 * (LEVEL-1), '*')||u.user_id u, 11 u.user_id, connect_by_root(permission) permission 12 FROM users u 13 LEFT JOIN permissions p ON u.user_id = p.user_id 14 CONNECT BY u.PARENT = PRIOR u.user_id 15 START WITH p.permission IS NOT NULL 16 ORDER SIBLINGS BY user_id; U USER_ID PERMISSION --------- ------- ---------- 3 3 B **4 4 B 1 1 A **2 2 A **3 3 A ****4 4 A 

Answer 2

You can take a look at http://www.adp-gmbh.ch/ora/sql/connect_by.html

Answer 3

It’s kind of black magic, but you can use table-cast-multiset to refer to one table from another in the WHERE clause:

 create table t1( usr number, parent number ); create table t2( usr number, perm char(1) ); insert into t1 values (1,null); insert into t1 values (2,1); insert into t1 values (3,1); insert into t1 values (4,3); insert into t2 values (1,'A'); insert into t2 values (3,'B'); select t1.usr , t2.perm from t1 , table(cast(multiset( select t.usr from t1 t connect by t.usr = prior t.parent start with t.usr = t1.usr ) as sys.odcinumberlist)) x , t2 where t2.usr = x.column_value ; 

In subquery x I create a table of all parents for a given user from t1 (including myself), and then attach it to the permissions for these parents.

Answer 4

Here is an example of just one user ID. you can use proc for all loops.

 CREATE TABLE a_lnk (user_id VARCHAR2(5), parent_id VARCHAR2(5)); CREATE TABLE b_perm (perm VARCHAR2(5), user_id VARCHAR2(5)); INSERT INTO a_lnk SELECT 1, NULL FROM DUAL; INSERT INTO a_lnk SELECT 2, 1 FROM DUAL; INSERT INTO a_lnk SELECT 3, 1 FROM DUAL; INSERT INTO a_lnk SELECT 4, 3 FROM DUAL; INSERT INTO b_perm SELECT 'A', 1 FROM DUAL; INSERT INTO b_perm SELECT 'B', 3 FROM DUAL; -- example for just for user id = 1 -- SELECT c.user_id, c.perm FROM b_perm c, (SELECT parent_id, user_id FROM a_lnk START WITH parent_id = 1 CONNECT BY PRIOR user_id = parent_id UNION SELECT parent_id, user_id FROM a_lnk START WITH parent_id IS NULL CONNECT BY PRIOR user_id = parent_id) d WHERE c.user_id = d.user_id UNION SELECT d.user_id, c.perm FROM b_perm c, (SELECT parent_id, user_id FROM a_lnk START WITH parent_id = 1 CONNECT BY PRIOR user_id = parent_id UNION SELECT parent_id, user_id FROM a_lnk START WITH parent_id IS NULL CONNECT BY PRIOR user_id = parent_id) d WHERE c.user_id = d.parent_id;