CODEWITHSUNDEEP
sqloracle-databasejoinview
Shree Naath
Shree Naath

Reputation: 493

How to create views with multiple tables?

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.

Upvotes: 0

Views: 324

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 1

Related Questions