how to create table based on multiple table in oracle

Question

so i have multiple tables and i want to create new table based on all columns in those multiple tables. but if the column name are duplicate, then take just 1 column.

i tried to create the query like this :

CREATE TABLE schema_rr_dmt
  AS (SELECT al.*, rl.*, cs.*  FROM a_loans al INNER JOIN s_CUSTRLTNP rl 
ON al.id_nasabah = rl.id_nasabah INNER JOIN s_customer cs ON cs.id_nasabah = rl.enterprise_id);

but it turns error

[Error] Script lines: 8-10 -------------------------
 ORA-00957: duplicate column name
 Script line 9, statement line 2, column 22

how to just take only one column if there's same column name from multiple tables

Answer 1

If the only duplicate names are from the join keys, then using can solve your problem:

CREATE TABLE schema_rr_dmt AS
    SELECT *
    FROM a_loans al INNER JOIN
         s_CUSTRLTNP rl 
         USING (id_nasabah) INNER JOIN
         s_customer cs 
         ON id_nasabah = rl.enterprise_id;

However, this doesn't work if there are other duplicate column names.