Data gets populated from a particular table's date instead of all table in sqlite

Question

I have three table namely trade, advance, and loan in which when I entered some values in loan it doesn't get displayed until I enter some values in trade for that particular date/month.

So can anyone tell me how I can overcome this issue?

SELECT strftime('%m-%Y', tradedate) as 'month', 
(SELECT IFNULL (SUM(FTL.tradebalanceamount),0 ) 
   FROM farmertradelabel as FTL 
   WHERE FTL.mobileno = '9486032141'  AND strftime('%m-%Y', FTL.tradedate) = strftime('%m-%Y', farmertradelabel.tradedate)
   ORDER BY strftime('%m-%Y', FTL.tradedate) DESC) AS tradeamount,
(SELECT IFNULL (SUM(advanceamount),0)  
   FROM advancelabel 
   WHERE advancelabel.mobileno = '9486032141' AND strftime('%m-%Y', advancelabel.advancedate) = strftime('%m-%Y', farmertradelabel.tradedate)
   ORDER BY strftime('%m-%Y', advancelabel.advancedate) DESC) AS advanceamount,
(SELECT IFNULL (SUM(loantotalamount),0) 
  FROM loanlabel 
  WHERE loanlabel.mobileno = '9486032141' AND strftime('%m-%Y', loanlabel.loandate) = strftime('%m-%Y', farmertradelabel.tradedate)
  ORDER BY strftime('%m-%Y', loanlabel.loandate) DESC) AS loanamount   
FROM farmertradelabel

Answer 1

Try the below.

What I have done is created a virtual table, containing unique months. To which there is info in one of the 3 tables for that month. Then the sums are bound to that month.

I have also removed the ORDER BY in the SUM clauses, as they don't change the result. Your IFNULL could also be changed, depending on how you expect it to work.

This method saves you writing out 3 lots of SUMs for each 3 fields (9 SUMs). If there are any typos, please fix accordingly.

NOTE that month is a keyword, in some SQL. So ideally, a better column name should be used.

SELECT month, 
(SELECT IFNULL (SUM(FTL.tradebalanceamount),0 ) 
   FROM farmertradelabel as FTL 
   WHERE FTL.mobileno = '9486032141'  AND strftime('%m-%Y', FTL.tradedate) = month) AS tradeamount,
(SELECT IFNULL (SUM(advanceamount),0)  
   FROM advancelabel 
   WHERE advancelabel.mobileno = '9486032141' AND strftime('%m-%Y', advancelabel.advancedate) = month) AS advanceamount,
(SELECT IFNULL (SUM(loantotalamount),0) 
  FROM loanlabel 
  WHERE loanlabel.mobileno = '9486032141' AND strftime('%m-%Y', loanlabel.loandate) = month) AS loanamount   

FROM
 (SELECT strftime('%m-%Y', tradedate) as 'month' FROM farmertradelabel
 UNION 
   SELECT  strftime('%m-%Y', advancedate) as 'month' FROM advancelabel
 UNION 
   SELECT strftime('%m-%Y', loandate) as 'month' FROM loanlabel
 ) as dateTable