Join based on derived table with a temp table

v 5.0.74

	Code:
	CREATE TABLE #ID (ID INT) SELECT ia2. FROM dbo.Table1 t LEFT OUTER JOIN (SELECT ia.Table2ID, ia.Table1ID FROM #ID i inner join dbo.Table2 ia ON i.ID = ia.Table2ID) ia2 ON ia2.Table1ID = t.Table1ID

In the Select clause, ia2. doesn't produce the column list for the ia2 derived table. If you remove the temp table, it works fine. Temp table on its own works fine. I don't know if this is new to 5 or has always been there.

5 is pretty solid so far. I haven't used the new features.

Unfortunately it cannot guess column names from a derived table based on the temp table which doesn't physically exist, it relies internally on certain SQL Server engine functions which in this case are unable needs to find temp table column names and definitions in the system catalog. This is not the same as parsing column names for the internal sub-query.

I guess, if you create the temp table, the suggestions for derived table columns will begin working as expected. Hope this makes sense.

That makes sense. However, it is able to figure out the columns if it was directly joined as below:

	Code:
	CREATE TABLE #ID (ID INT) SELECT i. FROM dbo.Table1 t LEFT OUTER JOIN #ID i ON ia2.Table1ID = i.Table1ID

That's correct. That's what I meant by "This is not the same as parsing column names for an internal sub-query" sorry for not being clear.

In case of derived tables, the entire subquery is evaluated as if it was a view. So that if any component of a view is missing, it cannot be evaluated.