We have a many to many relationship like below -
persons table captures ID (unique) and TYPE (ABC or XYZ)
create table persons (id number(20), type varchar2(20) check in ('ABC', 'XYZ'));
insert into persons (id, type) values (11, 'XYZ');
insert into persons (id, type) values (12, 'XYZ');
insert into persons (id, type) values (13, 'XYZ');
insert into persons (id, type) values (14, 'XYZ');
insert into persons (id, type) values (15, 'XYZ');
insert into persons (id, type) values (21, 'ABC');
insert into persons (id, type) values (22, 'ABC');
insert into persons (id, type) values (23, 'ABC');
insert into persons (id, type) values (24, 'ABC');
insert into persons (id, type) values (25, 'ABC');
relations table captures many to many relation between persons of type ABC and XYZ.
create table relations (abc_id number(20), xyz_id number(20) )
insert into relations (abc_id, xyz_id) values (21, 11);
insert into relations (abc_id, xyz_id) values (22, 11);
insert into relations (abc_id, xyz_id) values (22, 12);
insert into relations (abc_id, xyz_id) values (22, 13);
insert into relations (abc_id, xyz_id) values (23, 13);
insert into relations (abc_id, xyz_id) values (23, 14);
insert into relations (abc_id, xyz_id) values (24, 14);
insert into relations (abc_id, xyz_id) values (25, 15);
Is there a way to query relations table with input as abc_id=21 so that it returns all the other abc_id (21, 22, 23 and 24) ?
25 should not be returned.
Flow being - 21 → 11, 11 → 22, 22 → 13, 13 → 23, 23 → 14, 14 → 24.