sql selecting from view and table

Question

I have a select query in which I would like to do a join on a view and another table. The view works fine and so does the table, separately. But when I try to do something like:

select VIEW.col1, VIEW.col2
from VIEW, TABLE
where VIEW.col1 = TABLE.col1

I get The multi-part identifier "VIEW.col1" could not be bound.

Is the syntax wrong, or is that just not allowed on views?

Answer 1

This should work

select v.col1, t.col2
from VIEW v, TABLE t
where v.col1 = t.col1

Answer 2

SELECT VIEW.col1, VIEW.col2
from VIEW AS VIEW INNER JOIN TABLE AS TABLE
ON VIEW.col1 = TABLE.col1

Answer 3

SELECT MYVIEWNAME.col1, MYVIEWNAME.col2
FROM VIEWNAME AS MYVIEWNAME INNER JOIN TABLENAME AS MYTABLENAME
ON MYVIEWNAME.col1 = MYTABLENAME.col1

Answer 4

I suggest you use a left or inner join and alias the table and view.

select v.col1 , v.col2

 from view v
 inner join table t on t.col1 = v.col1

Answer 5

You have to have view like:

create table MyView
as
select column1 as col1, column2 as col2
from tab

or

CREATE VIEW MyView (col1,col2)
as
select column1, column2
from tab

then you can join with it:

select MyView.col1, MyView.col2
from MyView, TABLE
where MyView.col1 = MyView.col1

or

select v.col1, v.col2
from MyView v, TABLE t
where t.col1 = t.col1

or

select v.col1, v.col2
from MyView v
join TABLE t on t.col1 = t.col1

Maybe some examples will not work on all RDBMS.