following I am getting only matching entry not getting unmatching entry. I want both entry. if Transaction_date match both table then display both table amount ( C.CHEST_AMOUNT, C.BANK_AMOUNT) and if Transaction date is not matching then both table then display either C.CHEST_AMOUNT or C.BANK_AMOUNT.. Because i want display client when collection and bank entry done by data entry. if data entry done collection entry and bank entry make next date then I will display date wise entry and if data entry make both entry same date then it will must be display same entry.. i want display Transaction date wise entry..
Select C.ID, Upper(td.NAME) As 'Division', C.CHEST_AMOUNT, C.BANK_AMOUNT, CONVERT(NVARCHAR, C.TRANSACTION_DATE,105) 'Transaction_Date'
From tbl_div td
JOIN (Select TC.DIV_ID As 'ID', TC.TRANSACTION_DATE, sum(TC.cash + TC.cheque) 'Chest_Amount' , sum(TD.total_amount) 'Bank_Amount'
From tbl_chest_collection TC
JOIN tbl_bank_collection TD ON TC.ID = TD.ID And TC.TRANSACTION_DATE = TD.TRANSACTION_DATE
Group By TC.DIV_ID, TC.TRANSACTION_DATE
ON C.ID = td.ID