Hi
I have two tables, each with the following structure:
[tt]
INVOICE Table
INVOICE_ID int
INVOICE_DATE datetime (DD/MM/YY)
CUSTOMER string
INVOICE_ITEM Table
INVOICE_ID int
ITEM_NUMBER int
DESCRIPTION string
UNIT_PRICE float
QUANTITY int
[/tt]
And the following example data in them:
[tt]
INVOICE Table
1 30/01/09 CUSTOMER1
2 30/02/09 CUSTOMER1
3 30/03/09 CUSTOMER1
INVOICE_ITEM Table
1 1 SCREWDRIVER 10.00 5
1 2 HAMMER 11.00 1
1 3 DRILL 23.00 10
2 1 SCREWDRIVER 11.00 4
2 2 HAMMER 12.00 2
2 3 DRILL 24.00 11
3 1 SCREWDRIVER 10.50 6
3 2 HAMMER 11.50 3
3 3 DRILL 23.50 10
[/tt]
Between these two tables, the history of all the invoices to all customers is held, together with what items were on the invoices and at what price they were sold each time.
I'd like to construct a query which will give me the price at which an item was last sold, together with all the other details that go with that sale such as the invoice ID, quantity, customer name, etc. For example, if I wanted the latest price for a HAMMER sold to CUSTOMER1, I'd like the results to look like this:
[tt]
3 30/03/09 CUSTOMER1 2 HAMMER 11.50 3
[/tt]
Where the columns are as follows:
[tt]
INVOICE.INVOICE_ID
INVOICE.INVOICE_DATE
INVOICE.CUSTOMER
INVOICE_ITEM.ITEM_NUMBER
INVOICE_ITEM.DESCRIPTION
INVOICE_ITEM.UNIT_PRICE
INVOICE_ITEM.QUANTITY
[/tt]
I've got as far as returning just the item description and the date of the latest invoice with the following query:
[tt]
SELECT
INVOICE_ITEM.DESCRIPTION,
MAX(INVOICE.INVOICE_DATE)
FROM
INVOICE,
INVOICE_ITEM
WHERE
INVOICE.INVOICE_ID = INVOICE_ITEM.INVOICE_ID
GROUP BY
INVOICE_ITEM.DESCRIPTION
[/tt]
But I'm struggling to get all the other columns into the results set. I've tried various attempts using GROUP BY's and sub queries without much success. Can anyone suggest what I need to do? I'm pretty much a SQL beginner. Unfortunately I can't change the database structure at all, so that's out of the question.
Many thanks
Daniel
I have two tables, each with the following structure:
[tt]
INVOICE Table
INVOICE_ID int
INVOICE_DATE datetime (DD/MM/YY)
CUSTOMER string
INVOICE_ITEM Table
INVOICE_ID int
ITEM_NUMBER int
DESCRIPTION string
UNIT_PRICE float
QUANTITY int
[/tt]
And the following example data in them:
[tt]
INVOICE Table
1 30/01/09 CUSTOMER1
2 30/02/09 CUSTOMER1
3 30/03/09 CUSTOMER1
INVOICE_ITEM Table
1 1 SCREWDRIVER 10.00 5
1 2 HAMMER 11.00 1
1 3 DRILL 23.00 10
2 1 SCREWDRIVER 11.00 4
2 2 HAMMER 12.00 2
2 3 DRILL 24.00 11
3 1 SCREWDRIVER 10.50 6
3 2 HAMMER 11.50 3
3 3 DRILL 23.50 10
[/tt]
Between these two tables, the history of all the invoices to all customers is held, together with what items were on the invoices and at what price they were sold each time.
I'd like to construct a query which will give me the price at which an item was last sold, together with all the other details that go with that sale such as the invoice ID, quantity, customer name, etc. For example, if I wanted the latest price for a HAMMER sold to CUSTOMER1, I'd like the results to look like this:
[tt]
3 30/03/09 CUSTOMER1 2 HAMMER 11.50 3
[/tt]
Where the columns are as follows:
[tt]
INVOICE.INVOICE_ID
INVOICE.INVOICE_DATE
INVOICE.CUSTOMER
INVOICE_ITEM.ITEM_NUMBER
INVOICE_ITEM.DESCRIPTION
INVOICE_ITEM.UNIT_PRICE
INVOICE_ITEM.QUANTITY
[/tt]
I've got as far as returning just the item description and the date of the latest invoice with the following query:
[tt]
SELECT
INVOICE_ITEM.DESCRIPTION,
MAX(INVOICE.INVOICE_DATE)
FROM
INVOICE,
INVOICE_ITEM
WHERE
INVOICE.INVOICE_ID = INVOICE_ITEM.INVOICE_ID
GROUP BY
INVOICE_ITEM.DESCRIPTION
[/tt]
But I'm struggling to get all the other columns into the results set. I've tried various attempts using GROUP BY's and sub queries without much success. Can anyone suggest what I need to do? I'm pretty much a SQL beginner. Unfortunately I can't change the database structure at all, so that's out of the question.
Many thanks
Daniel