Joining table variable and derived table

In the quer开发者_StackOverflow社区y below @processtable is a table variable and temp is a derived table when I am trying to join them I am getting the following error:

The column prefix 't' does not match with a table name or alias name used in the query.

SELECT t.Order
       ,CONVERT(NVARCHAR, temp.[DateTime], 101) AS [DateTime] 
       ,t.Status
       ,t.Domain
       ,t.Media
       ,t.Approved      
       ,t.CreatedBy     
FROM @ProcessTable t
JOIN (SELECT MAX(Id),Order FROM OrderDetail OD WHERE  OD.Order = t.Order) temp
ON temp.Order = t.Order 
ORDER BY temp.[DateTime] DESC, Approved ASC

Try this:

SELECT     t.Order
        ,CONVERT(NVARCHAR,temp.[DateTime],101) AS [DateTime] 
        ,t.Status
        ,t.Domain
        ,t.Media
        ,t.Approved     
        ,t.CreatedBy        
    FROM @ProcessTable t
    JOIN (SELECT MAX(Id) AS LatestId, Order FROM OrderDetail OD GROUP BY Order)  temp
    ON temp.Order = t.Order 
    ORDER BY temp.[DateTime] DESC, Approved ASC

Note, I've remove the OD.Order = t.Order clause in the nested SELECT and added in a GROUP BY. The condition alongside that join (temp.Order = t.Order) does the filtering so doesn't need to be in the nested select - it was causing the error. Plus as you're using an aggregate function (MAX), you need the GROUP BY.

Also, I've given an alias for MAX(Id) to be returned as

You were almost right...You dont need the where clause in side the derived table if your going to include it as one of the Join criteria.

SELECT     
  t.Order
  ,CONVERT(NVARCHAR,temp.[DateTime],101) AS [DateTime] 
  ,t.Status
  ,t.Domain
  ,t.Media
  ,t.Approved     
  ,t.CreatedBy        
FROM @ProcessTable t
JOIN (SELECT MAX(Id) AS MaxID,Order, Max([DateTime]) AS [DateTime] FROM OrderDetail OD Group By Order)  temp
  ON temp.Order = t.Order 
ORDER BY temp.[DateTime] DESC, Approved ASC

I don't think you can reference the t.Order in the subquery as such. You should simply rely on the join to handle that clause.

E.g. simply remove the OD.Order = t.Order and allow the join clause to handle it.