By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE family(
Main_mem VARCHAR(27) NOT NULL PRIMARY KEY
,Neighnour_mem VARCHAR(3)
);
begin
INSERT INTO family(Main_mem,Neighnour_mem) VALUES ('101','-');
INSERT INTO family(Main_mem,Neighnour_mem) VALUES ('102','-');
INSERT INTO family(Main_mem,Neighnour_mem) VALUES ('103','102');
INSERT INTO family(Main_mem,Neighnour_mem) VALUES ('104','-');
INSERT INTO family(Main_mem,Neighnour_mem) VALUES ('105','-');
INSERT INTO family(Main_mem,Neighnour_mem) VALUES ('106','105');
INSERT INTO family(Main_mem,Neighnour_mem) VALUES ('107','-');
INSERT INTO family(Main_mem,Neighnour_mem) VALUES ('108','-');
INSERT INTO family(Main_mem,Neighnour_mem) VALUES ('109','108');
INSERT INTO family(Main_mem,Neighnour_mem) VALUES ('110','108');
end;
/
1 rows affected
select
f.main_mem,
coalesce(f1.main_mem, f.neighnour_mem) neighnour_mem
from family f
left join family f1 on f1.neighnour_mem = f.main_mem
where not exists (select 1 from family f2 where f2.main_mem = f.neighnour_mem)
order by 1, 2
| MAIN_MEM | NEIGHNOUR_MEM |
|---|---|
| 101 | - |
| 102 | 103 |
| 104 | - |
| 105 | 106 |
| 107 | - |
| 108 | 109 |
| 108 | 110 |