How to create a view from a join with multiple duplicate column names

aliasjoin;oracleview

I have a few tables with this sort of format

myTable: id | Table1_id | Table2_id | Table3_id | value

Table1: id | text | ...

Table2: id | text | ...

Table3: id | text | ...

I want to create a view that replaces the ID's with the associated text. I can get that using 3 join statements like shown:

SELECT table1.id, table2.id, table3.id, value
FROM myTable

INNER JOIN Table1
  ON Table1_id = Table1.id
INNER JOIN Table2
  ON Table2_id = Table2.id
INNER JOIN Table3
  ON Table3_id = Table3.id;

When I try to create a view using

CREATE VIEW myView AS (

SELECT table1.id, table2.id, table3.id, value
FROM myTable

   INNER JOIN Table1
     ON Table1_id = Table1.id  -- I tried as t1 but that doesn't work
   INNER JOIN Table2
     ON Table2_id = Table2.id
   INNER JOIN Table3
     ON Table3_id = Table3.id
);

I get an ORA-00957: duplicate column name error, which makes sense, they all have the same id column. How do I use an alias or something similar so I can create the view which has a format like this:

myTable: id | t1 | t2 | t3 | value

where tn is the column name that contains tn.text.

Best Answer

I figured it out, the alias goes up with the SELECT statement so the query would be:

CREATE VIEW myView as (

  SELECT table1.id as t1, table2.id as t2, table3.id as t3, value
  FROM myTable

  INNER JOIN Table1
    ON Table1_id = Table1.id
  INNER JOIN Table2
   ON Table2_id = Table2.id
  INNER JOIN Table3
   ON Table3_id = Table3.id

);
Related Question