How to create views with multiple tables?

Question

I have two tables as

CREATE TABLE table1(id NUMBER, name VARCHAR2(10));
CREATE TABLE table2(id NUMBER, dept VARCHAR2(4));

Now, I want to create a view containing id and name from the table table1 and dept from the table table2. I have a query like

CREATE VIEW table_view 
AS SELECT t1.id,t1.name,t2.dept 
FROM table1 t1 full outer join table2 t1 
ON t1.id = t2.id;

But I get the error: ORA-00904: "T2"."ID": invalid identifier. Help me clear the error. Thanks.

user330315 · Accepted Answer · 2016-10-18 06:02:34Z

1

You gave both tables the same alias outer join table2 t1 should be outer join table2 t2

CREATE VIEW table_view 
AS SELECT t1.id,t1.name,t2.dept 
FROM table1 t1 
   full outer join table2 t2 --<< here 
                ON t1.id = t2.id;

answered Oct 18, 2016 at 6:02

user330315

Sign up to request clarification or add additional context in comments.

1 Answer 1