Hierarchical Query with two tables

Question

I have this table named "AFP":

ID    || NAME || AFP_FUSION_ID
10    || afp1 ||  
20    || afp2 || 
30    || afp3 || 90
40    || afp4 || 30
50    || afp5 || 30
90    || afp6 ||

where "ID" is a primary key and "AFP_FUSION_ID" is a foreign key that references to the same table.

Also I have this table called "Registers":

Full Name || AFP_DESTINO
JOHN      || 10
PETER     || 20
ELENA     || 10
MARCO     || 90
DANIELA   || 30
FELIPE    || 40

where

AFP_DESTINO is a foreign key that references to AFP.ID

I have to create a function that has like parameter a number (10, 20, 30 , 40, 50 or 90) and has to return the corresponding information.

• If the function receives '10' ,the function has to return the data from "REGISTERS" where AFP_DESTINO = 10.
• If the function receives '30' has to return the data where AFP_DESTINO = 30, 40 or 50.
• And if the function receives '90', has to return the data where AFP_DESTINO = 90, 30, 40, 50.

I have to use Oracle, I have this:

select * from (select * from registers
full outer join afp
on registers.AFP_DESTINO = afp.ID) where afp_destino = 30 or afp_fusion_id = 30

(I have to improve this and transform to a function later). The problem with that is if I substitute '30' for '90', I will not have data when afp_destino = 40 or 50.

Can somebody help me, please?

UPDATE:

I have this code for resolve this:

select * from (select * from registers
full outer join afp
on registers.AFP_DESTINO = afp.ID)  connect by prior
afp_destino = afp_fusion_id start with afp_destino = 90

but generates inconsistencies, Appear more rows.

Answer 1

/*Assuming you have hierarchical data upto three levels
Hope this query may help you only if i got your problem correctly*/
CREATE TABLE AFP
(
ID integer,
Name varchar2(4),
AFP_FUSION_ID number(3,0)
);
INSERT INTO AFP VALUES(10,'afp1',NULL);
INSERT INTO AFP VALUES(20,'afp2',NULL);
INSERT INTO AFP VALUES(30,'afp3',90);
INSERT INTO AFP VALUES(40,'afp4',30);
INSERT INTO AFP VALUES(50,'afp5',30);
INSERT INTO AFP VALUES(90,'afp6',NULL);
CREATE TABLE Registers
(
Full_Name varchar2(10),
AFP_DESTINO number(3,0)
);
INSERT INTO Registers VALUES('Name1',10);
INSERT INTO Registers VALUES('Name2',20);
INSERT INTO Registers VALUES('Name3',10);
INSERT INTO Registers VALUES('Name4',90);
INSERT INTO Registers VALUES('Name5',30);
INSERT INTO Registers VALUES('Name6',40);
WITH AFP_TEST
AS
(Select AFP_1_2.ID_1    as ID_1,
        AFP_1_2.ID_2    as ID_2,
        AFP_3.ID        as ID_3,
        AFP_FUSION_ID_1 as AFP_FUSION_ID_1
from  (select   AFP_1.ID as ID_1,
                AFP_2.ID as ID_2,
                AFP_1.AFP_FUSION_ID as AFP_FUSION_ID_1
                from AFP AFP_1 left outer join AFP AFP_2
        ON AFP_1.ID = AFP_2.AFP_FUSION_ID) AFP_1_2 left outer join AFP AFP_3
        ON AFP_1_2.ID_2 = AFP_3.AFP_FUSION_ID
)
select *
from registers full outer join AFP_TEST afp 
                on registers.AFP_DESTINO = afp.ID_1
where afp_destino = 30 or afp_fusion_id_1 = 90
/*Output : http://sqlfiddle.com/#!4/4291c/3 */