Can't get this select join to work...

I get an error 'Multi-part identifier '#tmp1' could not be bound.
select #tmp1.col1 as mycol,
other columns...
from #tmp1, realtable
left join table2 on table2.id = #tmp1.col1
...
where realtable.id = table2.realtable
If I reverse the order in the from statement everything is ok but my results
don't seem right.
This statement did work in MySql.
Thanks,
JoeNot a clue what you are trying to do, but you have to realize that this is
not really a good idea, even in MySQL. What version of SQL Server. I
execute the following batch:
drop table #tmp1
drop table table2
drop table realtable
go
create table #tmp1
(
col1 int
)
create table realtable
(
id int
)
create table table2
(
realtable int
)
go
select #tmp1.col1 as mycol
from #tmp1,
realtable
left join table2
on table2.id = #tmp1.col1
where realtable.id = table2.realtable
and get:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix '#tmp1' does not match with a table name or alias name
used in the query.
You are cross joining #tmp1 with the results of realtable left joined with
table2, but your join criteria references the table you are cross joining
to. I don't think this even should be allowed. Change the comma to a cross
join and it will compile, though I am not sure you will get the correct
results.
select #tmp1.col1 as mycol
from #tmp1 cross join
realtable
left join table2
on table2.id = #tmp1.col1
where realtable.id = table2.realtable
Can you explain why the other criteria is in the where clause? I would have
expected:
select #tmp1.col1 as mycol,
other columns...
from #tmp1,
inner join realtable
on realtable.id = table2.realtable:
left outer join table2
on table2.id = #tmp1.col1
Though this may not be what you want, and it may be exactly what you are
doing anyhow, base on where you are doing the different joins.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:e9CVgK2AFHA.1452@.TK2MSFTNGP11.phx.gbl...
>I get an error 'Multi-part identifier '#tmp1' could not be bound.
> select #tmp1.col1 as mycol,
> other columns...
> from #tmp1, realtable
> left join table2 on table2.id = #tmp1.col1
> ...
> where realtable.id = table2.realtable
> If I reverse the order in the from statement everything is ok but my
> results
> don't seem right.
> This statement did work in MySql.
> Thanks,
> Joe
>