SQL JOIN WITH WHERE

I am trying to join a table variable table and another table.

@ProcessTbl      OrderDetail
Order            ID
Status           Order   
Approved         DateTime
Domain           Status  

OrderDetail table has multiple columns for same order开发者_Go百科. For example

ID  Order DateTime   Status
  1     1   11-17-10   Recived
  2     1   11-18-10   Processing
  3     1   11-19-10   shipped

so what i want the join to do is take the order number from @processtbl(table variable) and for max(id) get the datetime in this case the max id is 3

so my result should be

order  status    approved  domain    datetime
 1     shipped   true      finance   11-19-10




  SELECT   t.order
            ,[od.DateTime] 
            ,t.Status
            ,t.Domain
        ,t.Approved     
    FROM @ProcessTable t
    JOIN OrderDetail od ON od.order= t.order
    WHERE od.ID = (SELECT MAX(id) FROM orderdetail WHERE od.order = t.order ) 
    ORDER BY od.[DateTime], Approved ASC

But I am still getting duplicate records , looks like it is joning both the tables. How can I get distinct records?

select p.order, od.status, p.approved, p.domain, od.datetime 
from @ProcessTable p
inner join (
    select Order, max(ID) as MaxID
    from OrderDetail 
    group by Order
) odm
inner join OrderDetail od on odm.Order = od.Order 
    and odm.MaxID = od.ID

Just a thought, have you tried to perform a SELECT DISTINCT?

WHERE od.ID = (SELECT MAX(id) FROM orderdetail WHERE od.order = t.order ) 

The od.order is not referencing the orderdetail table in the sub query but the instance outside.

Try something like: WHERE od.ID = (SELECT MAX(id) FROM orderdetail as od1 WHERE od1.order = t.order )