Not 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 :)

[quoted text, click to view]

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