sql - Retrieving values from 3 tables -

i have 3 tables:

1. consumer has fields cons_id_no, key_id
2. bm_bill has fields key_id, bill_id_no, amt_payable, bill_date (it contain of bill amounts , date of consumer)
3. mreceipt has fields key_id, receipt_no, amt_paid, fine, pay_date (it contain of payment details of consumer)

the consumer table has 1 many relationship bm_bill , mreceipt. want create ledger information of consumer based on cons_id_no. should contain cons_id_no, key_id, bill_id_no (latest), bill_date (latest), amt_payable (latest),receipt_no (latest), amt_paid (latest), fine (latest), pay_date (latest) , have created below query

i have asked question here , found out 1 solution that, still not enough retrieving whole data tables of bills , payments. per tip got there, have reached solution, follows:

first of created type:

create or replace type key_id_row object     (         key_id number(10)     ); 

then created table type:

create or replace type key_id_tab table of key_id_row; 

then created function:

create or replace function get_key_id(cons_id varchar2) return key_id_tab     result_tab key_id_tab;     begin     select         key_id_row(key_id)     bulk collect          result_tab             consumer             cons_id_no = cons_id;     return result_tab;     end get_key_id; 

after used in query follows:

select     c.key_id,     c.cons_id_no consumerid,     b.bill_id_no,     b.key_id,     b.bill_date,     m.key_id,     m.receipt_no,     m.pay_date         consumer c left outer join     (         select             bb.bill_id_no,             gk.key_id,             bb.bill_date,             row_number() on (partition bb.key_id order bb.bill_date desc) rownumber                     bm_bill bb         join             table(get_key_id('2114109999')) gk         on             (gk.key_id = bb.key_id)         ) b on     (b.rownumber = 1) left outer join      (         select             gk.key_id,             mr.receipt_no,             mr.pay_date,             row_number() on (partition mr.key_id order mr.pay_date desc) rownumber                     mreceipt mr         join             table(get_key_id('2114109999')) gk         on             (gk.key_id = mr.key_id)     ) m on     (m.rownumber = 1)             c.cons_id_no='2114109999';  

is solution enough or have approach problem in following way:

select key_id consumer table separate query in:

select key_id consumer cons_id_no = '2114109999'; 

and store in variable , use value inside query mentioned above.

your solution creating table of key_ids associated each consumer id. if need latest answer on other thread correct. since aggregating latest values bill , payment tables sub-query join on ordered table best way go.

i not sure mean retrieving whole data bills , payments tables. if mean full table scans should relieved appropriate indexing and/or partitioning.

do intend ledger full list of bills , payments consumer? question gives impression care latest.