title: MySQL-how to make query for selecting opening recd issued closingbalance rate from two different table one is purchase and another is issued
body: <p>I want to make a query that retrieve data from two table i.e one is purchase and another is issue.
  both tables have same fields i.e icode,qty,rate,purdate and issuedate.
  query of purchase is:-SELECT Dry_Purchase.Icode, Sum(Dry_Purchase.Qty) AS SumOfQty, Dry_Purchase.Rate
  FROM Dry_Purchase
  WHERE (((Dry_Purchase.PurDate) Between DateSerial(Year(Date()),(Month(Date())-1),21) And DateSerial(Year(Date()),Month(Date()),20)))
  GROUP BY Dry_Purchase.Icode, Dry_Purchase.Rate;
  output of purchase query is:
  Icode   SumOfQty    Rate
  11  10      13.5
  11  39.5        14
  19  75      79.75
  19  22      80
  21  54      87.45
  23  15      218
  24  10.5        650
  8   79      33.25
  8   13      34</p>
  
  <p>query of issue is :- SELECT Dry_Expense.Icode, Sum(Dry_Expense.Qty) AS SumOfQty, Dry_Expense.Rate
  FROM Dry_Expense
  WHERE (((Dry_Expense.ExpDate) Between DateSerial(Year(Date()),(Month(Date())-1),21) And DateSerial(Year(Date()),Month(Date()),20)))
  GROUP BY Dry_Expense.Icode, Dry_Expense.Rate;
  output of this query is</p>
  
  <p>Icode   SumOfQty    Rate
  11  11.55       13
  11  8.55        13.5
  11  10.8        14
  19  2.35        80
  21  54      87.45
  8   15.9        33.25</p>
  
  <p>after combining above both query the output should like this
  rptdate icode opening recd   issued closingbal rate
  19/09/18  11   0        10    8.550  1.450     13.50
  19/09/18  11   0        39.5  10.800  28.700    14.00
  19/09/18  19   0        75    0.000   75       79.75
  19/09/18  19   0        22    2.350   72.650   80.00
  19/09/18  21   0        54    54       0       87.45
  19/09/18  23   0        15    0        15      218.00
  19/09/18  24   0        10.5  0        10.500  650.00
  19/09/18  8    0        79.0  15.900   63.100  33.25
  19/09/18  8    0        13.0   0       13.000  34.00
  19/09/18  8    11.550   0     11.550   0       13.00</p>
  
  <p>please help me how to make query for this output
  i am trying this query
  SELECT A.icode,A.qty,A.rate,A.recd as recd,B.Issued as Issue 
  FROM (SELECT icode,rate,purdate,SUM(Abs(qty)) AS recd 
  FROM Dry_Purchase GROUP BY icde,rate ) A,
  (SELECT icode,rate,expdate,(SUM(Abs(qty)) AS Issue 
   FROM Dry_Expense GROUP BY icode,rate) B
  WHERE A.icode=B.icode AND A.rate=B.rate AND
  (A.purdate Between DateSerial((Year(Date()),(Month(Date())-1),21)) And DateSerial(Year(Date()),Month(Date()),20)) 
   AND B.expdate Between DateSerial((Year(Date()),(Month(Date())-1),21) And DateSerial(Year(Date()),Month(Date()),20));
  please help me</p>