I am looking for the best way to get the last record from multiple tables.
For instance 4 tables.
Customer indexed on customer_id
Accounts has customer_id and indexed on account_id
Orders has account_id and indexed on order_id
Items has order_id and indexed on item_id
The way the database is set up there are no fields that are common across all the tables. I cannot edit the database structure. What I want to do is get a customer based on the customer id and then the latest account, latest order and latest item from the tables.
Currently I am doing this by using with statments and getting a max from each table based on the preceding table. Is there a more efficient way of doing this?