View

[tostay2003]

Can a view have more than one table in it. If at all yes. then how do i read from different tables. Like say I have

Code: Select all

VIEW  - Table1     -   ID1
                   -   ID2
                   -   Value1
      - Table2     -   ID2
                   -  Value2

Extract Requirement

ID1
ID2
Value1
Value2

do i need to say

Code: Select all

select  ID1,
         ID2,
         Value1,
         Value2 
from  VIEW 
where Table1.ID2=Table2.ID2

Am I in the right direction??

[chulett]

A view is just a logical construct that treats a result set like a table, and yes it can include more than one base table. It's already 'pre-joined', it's nothing you need to monkey with... all you may need to add are additional filters in the where clause you use to access it.

If the joins are inappropriate for what you need to do, get a new view.

If you are asking if you can join views to other tables, yes you can do that too - but it tends to be pretty darn inefficient.

[ray.wurlod]

If the view is defined with WHERE Table1.ID = Table2.ID then you don't need that particular WHERE clause in a SELECT from the view - indeed, it would generate an error.

[kumar_s]

The query to create the view would be

Code: Select all

Create View Veiw1 as Select a.ID1 As ID1, a.ID2, As ID2 a.Value1 As Value1, b.Value2 As Value2 from Table1 a, Table2 b where a.ID2 = b.ID2