I would like to join result of hierarchical query to set of other tables. I dont want to hardcode start with clause in the hierarchical query and would like the hierarchical query to depend on the join table result to do that.
Below is the sample dataset
create table person( id number, name varchar2(20));
insert into person(id, name) values (1,'Liam');
insert into person(id, name) values (2,'Noah');
insert into person(id, name) values (3,'Oliver');
insert into person(id, name) values (4,'Elijah');
insert into person(id, name) values (5,'William');
insert into person(id, name) values (6,'James');
insert into person(id, name) values (7,'Benjamin');
insert into person(id, name) values (8,'Lucas');
insert into person(id, name) values (9,'Henry');
insert into person(id, name) values (10,'Alexande');
insert into person(id, name) values (11,'Mason');
insert into person(id, name) values (12,'Michael');
insert into person(id, name) values (13,'Ethan');
insert into person(id, name) values (14,'Daniel');
insert into person(id, name) values (15,'Jacob');
insert into person(id, name) values (16,'Logan');
insert into person(id, name) values (17,'Jackson');
insert into person(id, name) values (18,'Levi');
insert into person(id, name) values (19,'Sebastia');
insert into person(id, name) values (20,'Mateo');
create table friends( person number, friend number);
insert into friends (person, friend) values (1,2);
insert into friends (person, friend) values (2,3);
insert into friends (person, friend) values (3,4);
insert into friends (person, friend) values (4,5);
insert into friends (person, friend) values (2,6);
insert into friends (person, friend) values (2,7);
insert into friends (person, friend) values (3,8);
insert into friends (person, friend) values (8,9);
insert into friends (person, friend) values (4,10);
insert into friends (person, friend) values (5,11);
insert into friends (person, friend) values (5,12);
insert into friends (person, friend) values (5,13);
insert into friends (person, friend) values (6,14);
insert into friends (person, friend) values (6,15);
insert into friends (person, friend) values (7,16);
insert into friends (person, friend) values (7,17);
insert into friends (person, friend) values (7,18);
insert into friends (person, friend) values (17,19);
insert into friends (person, friend) values (18,20);
create table party(organizerid number, partydate date )
insert into party(organizerid, partydate ) values (1, '01-Jan-2022');
insert into party(organizerid, partydate ) values (2, '01-Feb-2022');
insert into party(organizerid, partydate ) values (3, '01-Mar-2022');
insert into party(organizerid, partydate ) values (5, '01-Apr-2022');
insert into party(organizerid, partydate ) values (6, '01-May-2022');
insert into party(organizerid, partydate ) values (7, '01-Jun-2022');
I want to get all the friends and their friends for a person with a specific party date. Only caveat is, I cant give party date inside the hierarchical query as my requirement to keep it outside.
for example, I want to get the all the friends for a person with partydate 01-Mar-2022
I want to get the result like below. except, I dont want to hardcode value 3 in start with clause and I cant add party table as join inside hierarchal query as I have many other tables to join for my requirement.
select connect_by_root(f.person) root, sys_connect_by_path(f.person, '/') path, p.id, (p.name || ' is friend of ' || pf.name) friendlink, f.person, f.friend from friends f
join person p on f.person = p.id
join person pf on f.friend = pf.id
start with f.person = 3 /*hardcoded*/
connect by nocycle prior f.friend = f.person;
instead I want to achieve something like below, but not sure what to fill <------->
select hq.* from party pty
join (select connect_by_root(f.person) root, sys_connect_by_path(f.person, '/') path, p.id, (p.name || ' is friend of ' || pf.name) friendlink, f.person, f.friend from friends f
join person p on f.person = p.id
join person pf on f.friend = pf.id
start with f.person = <------->
connect by nocycle prior f.friend = f.person) hq on <---------->
where pty.partydate = '01-Mar-2022';
| ROOT | PATH | ID | FRIENDLINK | PERSON | FRIEND |
|---|---|---|---|---|---|
| 3 | /3 | 3 | Oliver is friend of Elijah | 3 | 4 |
| 3 | /3/4 | 4 | Elijah is friend of William | 4 | 5 |
| 3 | /3/4/5 | 5 | William is friend of Mason | 5 | 11 |
| 3 | /3/4/5 | 5 | William is friend of Michae | 5 | 12 |
| 3 | /3/4/5 | 5 | William is friend of Ethan | 5 | 13 |
| 3 | /3/4 | 4 | Elijah is friend of Alexande | 4 | 10 |
| 3 | /3 | 3 | Oliver is friend of Lucas | 3 | 8 |
| 3 | /3/8 | 8 | Lucas is friend of Henry | 8 | 9 |
Please guide me in the right direction. Thank you.
