I am having trouble trying to get a query to work that involves multiple tables and the use of the MAX function. I’m pretty sure a subquery is needed to get it to work but am having trouble figuring out how to do this.
I basically have three tables—Item, InvItem, and Invoice. The “Item” table keeps track of items that are for sale, the “Invoice” is an actual sales transaction, and “InvItem” are the individual items that are sold at each Invoice.
What I am trying to do is to return the last date that an item was last sold on and the number of the items sold during that last invoice for the particular item. I have no trouble getting the last date an item was sold on, but can’t figure out how to get the quantity of the item sold on that date. Here is the SQL to get the last date an item was sold on:
SELECT Max(Invoice.InvoiceDate) AS MaxOfInvoiceDate, InvItem.ItemID
FROM InvItem INNER JOIN Invoice ON InvItem.InvoiceID = Invoice.InvoiceID
GROUP BY InvItem.ItemID;
This gives me every "ItemID" that was sold on an invoice and the date of that invoice. However I can’t add “InvItem.Qty” to the SELECT statement since it isn’t going to be unique. I would think that I would need to do a subquery here but I’m a little unsure how to do it. I’ve done subqueries in the WHERE clause before on a single field but I would think I would need to use two fields here but I'm not even sure that would work either.
Does anyone have any suggestions? Any help is much appreciated. Thanks!