-1

enter image description here

(SELECT tr.rec as 'RRef', id.item as Product, tr.qty as 'In', '0' as 'Out', 
    rm.daterec as 'DATE' 
    FROM details as id 
    INNER JOIN receive AS tr ON tr.recdes = id.item 
    INNER JOIN receivedmain AS rm ON rm.rec = tr.rec
    WHERE rm.daterec BETWEEN '2020/01/01' and '2020/02/13')
union all
    (SELECT tst.transno as 'TReference', i.item as Product, '0' as 'In', 
     tst.fld_qty as 'Transfer Qty', tmt.fld_transdate as 'DATE' 
     FROM itemdetails as i 
     LEFT JOIN subtransfer AS tst ON tst.name = i.item 
     LEFT join maintransfer as tmt on tmt.transno = tst.transno 
     WHERE tmt.transdate between '2020/01/01' and '2020/02/13' and 
     (tmt.status = 4 or tmt.status = 5)) order by DATE
New contributor
user201105 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Unclear. Does you want to combine 2 output records (onу from each subquery) into one? if so specify the fields which have the same value and may be combined. – Akina Feb 14 at 6:43
  • I've had add photo of the sample results of my posted sql syntax. what happening is when i sort in 'DATE' it's sorting by individual select statements not the whole result. – user201105 Feb 14 at 6:48
  • What are datatypes for receivedmain.daterec and maintransfer.fld_transdate??? It seems they are of string type - you must convert them into DATE datatype before sorting. – Akina Feb 14 at 6:52
  • I knew it! thank you Akina. Maybe I need to take some rest for being so analytic that didn't saw simple problem like this. the problem is in the tmt.transdate it's a varchar data type. – user201105 Feb 14 at 7:03
  • I see you use date literals like '2020/02/13' whereas MySQL uses '2020-02-13' format for date literals. This may cause implicit convertion into VARCHAR. Use correct literal format. – Akina Feb 14 at 7:10

Browse other questions tagged or ask your own question.