By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table names (id, name) as
select 123, 'Alex' from dual
union all
select 343, 'Flynn' from dual;
2 rows affected
alter table names
add constraint person_pk
primary key (id);
create table postal_address (id, postal_address) as
select 123, 'Decker Hill A' from dual
union all
select 343, 'Apartment A' from dual;
2 rows affected
alter table postal_address
add constraint post_fk_names
foreign key (id) references names (id);
create table electronic_address (id, phone_type, phone_number) as
select 123, 'Mobile', 99776 from dual
union all
select 123, 'Mobile', 99876 from dual
union all
select 343, 'Mobile', 98798 from dual;
3 rows affected
alter table electronic_address
add constraint elec_fk_names
foreign key (id) references names (id);
select n.id, n.name, 1 as address_type, e.phone_type, e.phone_number, null as postal_address
from names n
join electronic_address e on e.id = n.id
union all
select n.id, n.name, 2, null, null, p.postal_address
from names n
join postal_address p on p.id = n.id
order by id, address_type;
| ID | NAME | ADDRESS_TYPE | PHONE_TYPE | PHONE_NUMBER | POSTAL_ADDRESS |
|---|---|---|---|---|---|
| 123 | Alex | 1 | Mobile | 99776 | null |
| 123 | Alex | 1 | Mobile | 99876 | null |
| 123 | Alex | 2 | null | null | Decker Hill A |
| 343 | Flynn | 1 | Mobile | 98798 | null |
| 343 | Flynn | 2 | null | null | Apartment A |
select n.id,
n.name,
e.phone_type,
e.phone_number,
p.postal_address
from names n
left join electronic_address e on e.id = n.id
left join postal_address p on p.id = n.id
order by id;
| ID | NAME | PHONE_TYPE | PHONE_NUMBER | POSTAL_ADDRESS |
|---|---|---|---|---|
| 123 | Alex | Mobile | 99776 | Decker Hill A |
| 123 | Alex | Mobile | 99876 | Decker Hill A |
| 343 | Flynn | Mobile | 98798 | Apartment A |