I have two tables with a one to many relationship. IMItmIdx_SQL (master item record, one each) and IMInvTrx_SQL (Item transaction records, many per item). I want to return information from both tables. I want the Item master record and the most recent Transacton record.
In the past when I wanted to do this, I would write a query like the one below.
(SELECT TOP 1 T.trx_dt FROM IMINVTRX_SQL T
WHERE (T.item_no = Item_No) AND (T.source = 'R') AND (T.doc_type = 'R')
ORDER BY T.trx_dt DESC) AS TrxDt
WHERE (item_no = '10-803-00')
The limitation with this syntax is that I can only return the one field from the Transaction table, Trx_Dt. In this instance I need to return more than one field from the Transactoin table. I need to return, Trx_Dt, Quantity, Unit_cost.
What is the best way to accomplish this in SQL 2000?