Ramesh R Ramesh R - 1 month ago 16
Android Question

How to find the minimum and maximum dates from three tables even if there is no date in one table in android sqlite

I am having three tables namely trade,advance and loan i want the minimum and maximum dates from these tables even if a table doesn't contains any dates but the query which i am using returns null even if there is no date in that particular table.I want the get the minimum and maximum dates from these three tables

SELECT min(mx), max(mn) " +
"FROM (" +
" SELECT min(advancedate) AS mx, max(advancedate) AS mn FROM advancelabel WHERE mobileno = " + farmermobno + " " +
" UNION ALL" +
" SELECT min(tradedate) AS mx, max(tradedate) AS mn FROM farmertradelabel WHERE mobileno = " + farmermobno + " " +
" UNION ALL" +
" SELECT min(loandate) AS mx,max(loandate)AS mn FROM loanlabel WHERE mobileno = " + farmermobno + " " +
" ) AS t1";


can anyone tell me what is wrong in this query.

Answer

check this screnshot when no record available in any table.

http://prnt.sc/cy7bti

check this screenshot for min,max date from database

http://prnt.sc/cy7cdm

query is like simple same like as your query.

select min(mindate),max(maxdate) from (select max(date1) as maxdate,min(date1) as mindate from table1 union all select max(date2) as maxdate,min(date2) as mindate from table2)