Hi all
Oracle version 11.2
wih invoice_tbl -- parent
as
select 1 inv_id, 50 amount from dual
union all
select 2 inv_id, 50 amount from dual
union all
select 4 inv_id, 50 amount from dual
union all
select 5 inv_id, 50 amount from dual;
wih check_tbl -- child
as
select 1111 check_no, 1010 chk_id, 100 amount from dual
union all
select 2222 check_no, 2020 chk_id, 200 amount from dual;
wih check_det_tbl -- child details
as
select 1010 chk_id, 1 inv_id, 50 amount from dual
union all
select 1010 chk_id, 2 inv_id, 50 amount from dual
union all
select 1010 chk_id, 3 inv_id, 50 amount from dual --this invoice is missing in invoice_tbl table
union all
select 3030 chk_id, 3 inv_id, 50 amount from dual ---
union all
select 2020 chk_id, 5 inv_id, 50 amount from dual;
-- here is teh join conditions
select *
from invoice_tbl aia,
check_tbl chk,
check_det_tbl chk_line
where aia.inv_id=chk_line.inv_id
and chk.chk_id=chk_line.chk_id
-- missing invoices for checks
3 inv_id is missing in invoice_tbl table i want to bring this.
-- missing checks for invoices
3030 chk_id is missing in check_tbl table i want to bring this.
next repeat the step1 and setp2 until...