1 Reply
Aug 07, 2020 11:31 am
Try this below query
create table ksrtc(path varchar2(100), distance number);
insert into ksrtc values ('Hyderabad -> Bangalore', 590);
insert into ksrtc values ('Bangalore -> Hyderabad', 590);
insert into ksrtc values ('Delhi -> Agra', 235);
insert into ksrtc values ('Agra -> Delhi', 235);
insert into ksrtc values ('Hyderabad -> Shiridi', 590);
SELECT * FROM ksrtc
MINUS
SELECT k1.* FROM ksrtc k1, ksrtc k2
WHERE substr(k1.path, 1, instr(k1.path, '-',1)-2) = substr(k2.path, instr(k2.path, '-',1)+3)
AND substr(k2.path, 1, instr(k2.path, '-',1)-2) = substr(k1.path, instr(k1.path, '-',1)+3)
AND k1.distance = k2.distance AND k1.rowid < k2.rowid;