Retrieving Values From 3 Tables

I have three tables: Consumer which has fields cons_id_no, key_id bm_bill which has fields key_id, bill_id_no, amt_payable, bill_date (It will contain all of bill amounts and date

Solution 1:

Your solution currently is creating table of the key_ids associated with each consumer id. If you only need the latest then the answer on your other thread is correct. Since you are aggregating the latest values from your bill and payment tables a sub-query join on an ordered table is the best way to go.

I am not sure what you mean by retrieving whole data from the bills and payments tables. If you mean full table scans then this should be relieved by appropriate indexing and/or partitioning.

Do you intend your ledger to be a full list of all bills and payments by the consumer? Your question gives the impression you only care about the latest.