Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table_1 (mykey int, a char, a_field varchar2(15));
create table table_2 (mykey int, b char, c char);
create table table_3 (mykey int, d char);
insert into table_1 (mykey, a, a_field) values (0, 'a', 'Some Selection');
1 rows affected

insert into table_2 (mykey, b, c) values (0, 'b', 'c');
1 rows affected

insert into table_3 (mykey, d) values (0, 'd');
1 rows affected

-- Works if table_1, table_2 and table_3 have no other column in common than mykey.
CREATE TABLE Temptable AS
SELECT *
FROM
table_1
INNER JOIN table_2 USING (mykey)
INNER JOIN table_3 USING (mykey)
WHERE table_1.A_FIELD = 'Some Selection';
1 rows affected

alter table table_3 add a_field varchar2(15);
update table_3 set a_field = 'A from table 3';
1 rows affected

--CREATE TABLE Temptable2 AS
WITH table_3_r as (SELECT * FROM table_3 PIVOT (max(1) FOR (a_field) IN ()))
SELECT *
FROM
table_1
INNER JOIN table_2 USING (mykey)
INNER JOIN table_3_r USING (mykey)
WHERE table_1.A_FIELD = 'Some Selection';


MYKEY
A
A_FIELD
B
C
D


0
a
Some Selection
b
c
d