Get records of two tables with the same date

Question

I am stuck in a problem. After several hours of work, I could not find a suitable solution.

So, first What I'm trying to do: I have two sales and purchase tables, both have data according to dates.

---

The data is similar to mysql:

Sale:

  sale1  |  25-07-14  | 2500
  sale2  |  26-07-14  | 2200
  sale3  |  27-07-14  | 0
  sale4  |  27-07-14  | 1500
  sale5  |  28-07-14  | 1000

Purchase:

  purchase1  |  25-07-14  | 0
  purchase2  |  26-07-14  | 1000
  purchase3  |  27-07-14  | 2500
  purchase4  |  28-07-14  | 0
  purchase5  |  28-07-14  | 100

---

Now I want to show my data as follows:

Date| sale(sum)  | purchase(sum)
Date| sale(sum)  | purchase(sum)
Date| sale(sum)  | purchase(sum)
Date| sale(sum)  | purchase(sum)

I need date data from both tables.

this is what i tried:

SELECT date, sum(purchase_remaining) as a FROM purchase group by date
UNION
SELECT sale_date, sum(sale_balance) as b FROM sale group by sale_date

The result of this query:

date | purchase
date | purchase
date | purchase
date | sale
date | sale
date | sale 

Any idea how I can achieve this.

Answer 1

Join the tables.

select date, sum(purchase_remaining) a
, sum(sale_balance) b

from purchase join sale on purchase.date = sale.date
group by date