Description:
In the below query FULL OUTER JOIN is not working
SELECT *
from ((Select a.office_id as oid, o.display_name as bname,p.personnel_id as pid, p.display_name as KendraManager,
sum(ifnull(l.interest_paid,0)) as InterestPaid
From mifos.account a
left join mifos.loan_account la on mifos.la.account_id = mifos.a.account_id
left join mifos.loan_schedule l on mifos.l.account_id = mifos.a.account_id
left join mifos.personnel p on (p.personnel_id=a.PERSONNEL_ID)
inner join mifos.office o on(o.office_id=a.office_id)
Where l.id>=(select loan_schedule_id from mifos.max_rec where creation_date=curdate()-1) and l.action_date =curdate()-1
and (l.payment_date = curdate()-1 or l.payment_status=0)
and (mifos.a.account_state_id = 5 or mifos.a.account_state_id = 9 or (mifos.a.account_state_id <> 10 and mifos.a.closed_date =curdate()-1))
and a.office_id in(12)
group by a.office_id, p.personnel_id) as interest
JOIN
(select a.office_id as oid,p.display_name as bname,p.personnel_id as pid, p.display_name as Kmanager,sum(ca.amount)as fineAmount
from mifos.account a,mifos.customer_account_activity ca, personnel p
Where a.account_id = ca.account_id
and p.personnel_id=a.personnel_id
and ca.customer_account_activity_id>=(select customer_account_Activity_id from mifos.max_rec where creation_date=curdate()-1)
and a.account_type_id=3
and ca.created_date=curdate()-1
and ca.description='Payment rcvd.'
and a.office_id in(12)
group by a.office_id, p.personnel_id) fine on (interest.oid=fine.oid and interest.pid=fine.pid ))
group by Interest.oid,Interest.KendraManager;
How to repeat:
for your information
First Query will return 8 rows.. &
Second Query will return 5 rows.. &
if its Common is pid. then returns only all 8 rows..
