Miguel Miguel - 1 month ago 15
SQL Question

Hierarchy Query with join

I have a table with user id's and another table with hierarchy elements. The table with the user id has a unique key to the table that has the hierarchy elements. What I'm trying to accomplish is to display every element of the
hierarchy table by user. The problem is that I have hierarchy nodes that I need to traverse and display. Below is an example of the tables that I'm using and the data that I'm using.

create table fsecure(
userid varchar(100),
f_element varchar(6)
);

REM INSERTING into FSECURE
SET DEFINE OFF;
Insert into FSECURE (USERID,F_ELEMENT) values ('FFA','140');
Insert into FSECURE (USERID,F_ELEMENT) values ('FFA','280');
Insert into FSECURE (USERID,F_ELEMENT) values ('GGA','202118');
Insert into FSECURE (USERID,F_ELEMENT) values ('GGA','140');
Insert into FSECURE (USERID,F_ELEMENT) values ('RFA','202116');



CREATE TABLE "FVAL"
( "FUND" VARCHAR2(6 ),
"FUND_PRED" VARCHAR2(6)
)

REM INSERTING into FVAL
SET DEFINE OFF;
Insert into FVAL (FUND,FUND_PRED) values ('140',null);
Insert into FVAL (FUND,FUND_PRED) values ('280',null);
Insert into FVAL (FUND,FUND_PRED) values ('2000','140');
Insert into FVAL (FUND,FUND_PRED) values ('20001','2000');
Insert into FVAL (FUND,FUND_PRED) values ('20002','2000');
Insert into FVAL (FUND,FUND_PRED) values ('20003','2000');
Insert into FVAL (FUND,FUND_PRED) values ('202118','2000');
Insert into FVAL (FUND,FUND_PRED) values ('2800','280');
Insert into FVAL (FUND,FUND_PRED) values ('280001','2800');
Insert into FVAL (FUND,FUND_PRED) values ('280002','2800');
Insert into FVAL (FUND,FUND_PRED) values ('280003','2800');
Insert into FVAL (FUND,FUND_PRED) values ('280004','2800');


here is a query that works for when I'm doing this for you just one user.

SELECT F.FUND
FROM FVAL F
CONNECT BY PRIOR F.FUND = F.FUND_PRED
START WITH F.FUND IN ( SELECT F_ELEMENT FROM FSECURE WHERE USERID = 'FFA' );


I would like to make this generic enough to display the element with every user from fsecure.

expected output (Note: I'm only showing one user but I would like to do this for every user on table fsecure)

"FUND" "'FFA'"
"140" "FFA"
"2000" "FFA"
"20001" "FFA"
"20002" "FFA"
"20003" "FFA"
"202118" "FFA"
"280" "FFA"
"2800" "FFA"
"280001" "FFA"
"280002" "FFA"
"280003" "FFA"
"280004" "FFA"


Any help would be greatly appreciated.

Answer

I think this does what you're after:

SELECT     fv.fund,
           connect_by_root(fs.userid) userid
FROM       fval fv
           FULL OUTER JOIN fsecure fs ON (fs.f_element = fv.fund) -- maybe left outer join?
CONNECT BY PRIOR fv.fund = fv.fund_pred
START WITH fs.userid IS NOT NULL
ORDER BY   connect_by_root(fs.userid),
           connect_by_root(fs.f_element),
           LEVEL,
           fv.fund;

FUND   USERID
------ ------
140    FFA
2000   FFA
20001  FFA
20002  FFA
20003  FFA
202118 FFA
280    FFA
2800   FFA
280001 FFA
280002 FFA
280003 FFA
280004 FFA
140    GGA
2000   GGA
20001  GGA
20002  GGA
20003  GGA
202118 GGA
202118 GGA
       RFA

N.B. I wasn't sure if you wanted the RFA userid to be displayed in the output, since it doesn't have a corresponding fund in the fval table. If you don't want the RFA userid in the output then simply convert the full outer join into a left outer join instead.