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.