I have to write a view that will return all invoices, but with the contact person of the last invoice issued for the same product.

It is hard to explain so I will try to show on example:

Table1: "Invoice"
Table2: "Invoice_row"
Table3: "Contacts"

Each Invoice record contains Contact_id field which points to the contact person who order it.
Each Invoice_row record contains product information.

Products are based on codes (1100, 1101, ...) and to make things a bit harder, products have updates which have the same base number: 110001, 110002, 110101, ...

I would like to get:
Company, Product, Contact, InvoiceNo, InvoiceDate

I can get this with the contact person stated on the individual invoice record, but I don't know how to get the last contact person for each product:

If John ordered 1100 and then Jane ordered 110001 (which is the update to 1100 and both of them are contacts for them same company) I would like to get:

InvoiceNo 1, Product 1100, Contact person Jane
InvoiceNo 2, Product 110001, Contact person Jane

Makes any sense? I want Jane to be returned for all records.

I realy need this so I would appreciate any help.
If something is not clear I will try to explain again.

-thanks

solved