CODEWITHSUNDEEP
sqlview
sd_dracula
sd_dracula

Reputation: 3896

sql selecting from view and table

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?

Upvotes: 6

Views: 104842

Answers (5)

Robert
Robert

Reputation: 25753

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.

Upvotes: 0

Pierce
Pierce

Reputation: 11

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

Upvotes: 0

user751651
user751651

Reputation: 

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

Upvotes: 1

Vytalyi
Vytalyi

Reputation: 1695

This should work

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

Upvotes: 11

CatchingMonkey
CatchingMonkey

Reputation: 1391

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

Upvotes: 1

Related Questions