query that uses join to return singe record from multiple entries in join table

I'm trying to use a query run on my main table (with single unique records) to get corresponding details from another table/view that has multiple entries related to each record in the first.

When I do an inner join on the query all the entries from the second table are included in the results, and what I really want is to get a single record based on the maximum date of all the entries related to a single product.

In my code snippet the ITEM_CODE is the identifier across both tables and in the second table (with the multiple entries) the ORDER_DATE is the value I want to filter the join on (hope this makes sense)

This is driving me bananas

SELECT    table1.PHYSICAL, table1.LOCATION, table1.STD_VAL, table1.AVE_VAL, table1.MINIMUM,table1.ITEM_CODE, table2.ORDER_DATE, table2.ORDER_REF, table2.STOCK_QTY,table2.AMT_13, table2.SUPP_CODE, table2.ACCNT_NAME
FROM table1 INNER JOIN
table2 ON table1.ITEM_CODE = table2.ITEM_CODE
WHERE table1.LOCATION <> '99' AND table1.ITEM_CODE = (SELECT TOP 1 ITEM_CODE FROM table2 WHERE ITEM_CODE = table1.ITEM_CODE)
ORDER BY table1.ITEM_CODE

Select allOpen in new window