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.
CodePudding user response：
You may use lateral join to pass a column to the correlated subquery, which allows to emulate its execution for each input row:
select pty.partydate, hq.* from party pty left join lateral ( select connect_by_root(f.person) as root, sys_connect_by_path(f.person, '/') as path, p.id, (p.name || ' is friend of ' || pf.name) as 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 = pty.organizerid connect by nocycle prior f.friend = f.person ) hq on pty.organizerid = hq.root -- where pty.partydate = date '2022-03-01' order by partydate, root, pathPARTYDATE | ROOT | PATH | ID | FRIENDLINK | PERSON | FRIEND :--------- | ---: | :--------- | -: | :---------------------------- | -----: | -----: 2022-01-01 | 1 | /1 | 1 | Liam is friend of Noah | 1 | 2 2022-01-01 | 1 | /1/2 | 2 | Noah is friend of James | 2 | 6 2022-01-01 | 1 | /1/2 | 2 | Noah is friend of Oliver | 2 | 3 2022-01-01 | 1 | /1/2 | 2 | Noah is friend of Benjamin | 2 | 7 2022-01-01 | 1 | /1/2/3 | 3 | Oliver is friend of Lucas | 3 | 8 2022-01-01 | 1 | /1/2/3 | 3 | Oliver is friend of Elijah | 3 | 4 2022-01-01 | 1 | /1/2/3/4 | 4 | Elijah is friend of William | 4 | 5 2022-01-01 | 1 | /1/2/3/4 | 4 | Elijah is friend of Alexande | 4 | 10 2022-01-01 | 1 | /1/2/3/4/5 | 5 | William is friend of Michael | 5 | 12 2022-01-01 | 1 | /1/2/3/4/5 | 5 | William is friend of Mason | 5 | 11 2022-01-01 | 1 | /1/2/3/4/5 | 5 | William is friend of Ethan | 5 | 13 2022-01-01 | 1 | /1/2/3/8 | 8 | Lucas is friend of Henry | 8 | 9 2022-01-01 | 1 | /1/2/6 | 6 | James is friend of Daniel | 6 | 14 2022-01-01 | 1 | /1/2/6 | 6 | James is friend of Jacob | 6 | 15 2022-01-01 | 1 | /1/2/7 | 7 | Benjamin is friend of Logan | 7 | 16 2022-01-01 | 1 | /1/2/7 | 7 | Benjamin is friend of Levi | 7 | 18 2022-01-01 | 1 | /1/2/7 | 7 | Benjamin is friend of Jackson | 7 | 17 2022-01-01 | 1 | /1/2/7/17 | 17 | Jackson is friend of Sebastia | 17 | 19 2022-01-01 | 1 | /1/2/7/18 | 18 | Levi is friend of Mateo | 18 | 20 2022-02-01 | 2 | /2 | 2 | Noah is friend of Oliver | 2 | 3 2022-02-01 | 2 | /2 | 2 | Noah is friend of James | 2 | 6 2022-02-01 | 2 | /2 | 2 | Noah is friend of Benjamin | 2 | 7 2022-02-01 | 2 | /2/3 | 3 | Oliver is friend of Elijah | 3 | 4 2022-02-01 | 2 | /2/3 | 3 | Oliver is friend of Lucas | 3 | 8 2022-02-01 | 2 | /2/3/4 | 4 | Elijah is friend of Alexande | 4 | 10 2022-02-01 | 2 | /2/3/4 | 4 | Elijah is friend of William | 4 | 5 2022-02-01 | 2 | /2/3/4/5 | 5 | William is friend of Ethan | 5 | 13 2022-02-01 | 2 | /2/3/4/5 | 5 | William is friend of Michael | 5 | 12 2022-02-01 | 2 | /2/3/4/5 | 5 | William is friend of Mason | 5 | 11 2022-02-01 | 2 | /2/3/8 | 8 | Lucas is friend of Henry | 8 | 9 2022-02-01 | 2 | /2/6 | 6 | James is friend of Jacob | 6 | 15 2022-02-01 | 2 | /2/6 | 6 | James is friend of Daniel | 6 | 14 2022-02-01 | 2 | /2/7 | 7 | Benjamin is friend of Jackson | 7 | 17 2022-02-01 | 2 | /2/7 | 7 | Benjamin is friend of Logan | 7 | 16 2022-02-01 | 2 | /2/7 | 7 | Benjamin is friend of Levi | 7 | 18 2022-02-01 | 2 | /2/7/17 | 17 | Jackson is friend of Sebastia | 17 | 19 2022-02-01 | 2 | /2/7/18 | 18 | Levi is friend of Mateo | 18 | 20 2022-03-01 | 3 | /3 | 3 | Oliver is friend of Elijah | 3 | 4 2022-03-01 | 3 | /3 | 3 | Oliver is friend of Lucas | 3 | 8 2022-03-01 | 3 | /3/4 | 4 | Elijah is friend of Alexande | 4 | 10 2022-03-01 | 3 | /3/4 | 4 | Elijah is friend of William | 4 | 5 2022-03-01 | 3 | /3/4/5 | 5 | William is friend of Ethan | 5 | 13 2022-03-01 | 3 | /3/4/5 | 5 | William is friend of Michael | 5 | 12 2022-03-01 | 3 | /3/4/5 | 5 | William is friend of Mason | 5 | 11 2022-03-01 | 3 | /3/8 | 8 | Lucas is friend of Henry | 8 | 9 2022-04-01 | 5 | /5 | 5 | William is friend of Mason | 5 | 11 2022-04-01 | 5 | /5 | 5 | William is friend of Michael | 5 | 12 2022-04-01 | 5 | /5 | 5 | William is friend of Ethan | 5 | 13 2022-05-01 | 6 | /6 | 6 | James is friend of Daniel | 6 | 14 2022-05-01 | 6 | /6 | 6 | James is friend of Jacob | 6 | 15 2022-06-01 | 7 | /7 | 7 | Benjamin is friend of Jackson | 7 | 17 2022-06-01 | 7 | /7 | 7 | Benjamin is friend of Logan | 7 | 16 2022-06-01 | 7 | /7 | 7 | Benjamin is friend of Levi | 7 | 18 2022-06-01 | 7 | /7/17 | 17 | Jackson is friend of Sebastia | 17 | 19 2022-06-01 | 7 | /7/18 | 18 | Levi is friend of Mateo | 18 | 20
db<>fiddle here
CodePudding user response：
I want to get the all the friends for a person with
partydate01-Mar-2022
You can use your query and replace the hardcoded START WITH with an EXISTS condition:
select connect_by_root(f.person) AS root,
sys_connect_by_path(f.person, '/') AS path,
p.id,
p.name || ' is friend of ' || pf.name AS 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
EXISTS(
SELECT 1
FROM party p
WHERE partydate = DATE '2022-03-01'
AND f.person = p.organizerid
)
connect by nocycle prior f.friend = f.person;
Which, for your sample data, outputs:
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 Michael 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
db<>fiddle here