mysql query: show summary of all payments made and total of invoices by date

I want this kind of result

from these tables.

I even can't figure out how to do it with php. I even tried to join payment and invoice table on date but in vain.

It's a purchase system and this query will show summary of all payments m开发者_如何学运维ade and total of invoices by date.

I thought of a solution that first select all dates from invoices and then select all dates from payments and take their union. Then check if there is an invoice on that date and then check if there is a payment on that date. But this way there will be too many queries.

select
  x.date,
  sum(x.invoiceTotal) as invoiceTotal,
  sum(x.paymentsMade) as paymentMade
from
 (select
   i.date, 
   sum(i.rate * i.quantity /*?*/) as invoiceTotal,
   null as paymentMade    
 from
   invoice i
   inner join invoiceitem ii on ii.invoiceId = i.invoiceId
 group by
   i.date
 union all
 select
   p.date,
   null as invoiceTotal,
   sum(p.amount) as paymentMade    
 from
   payment p
 group by
   p.date) x
group by
  x.date
order by
  x.date

You need to look at your joins. Look at this website:

http://www.wellho.net/solutions/mysql-mysql-joins-using-left-join-and-right-join-to-find-orphan-rows.html

I believe you should also look here because you want the joins to occur simultaneiously:

http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/

Use your aggregates as appropriate.

    select
  x.date,
  sum(x.invoiceTotal) as invoiceTotal,
  sum(x.paymentMade) as paymentMade
from
(SELECT
Sum((invoiceitem.rate * invoiceitem.quantity)) AS invoiceTotal,
invoice.`date`,
null AS paymentMade
FROM
      invoice
      Left Join invoiceitem ON invoice.invoiceId = invoiceitem.invoiceId
where companyId='6'
GROUP BY
      invoice.`date`
UNION ALL
SELECT
null as invoiceTotal,
  payment.date,
  sum(payment.amount) as paymentMade    
 from
  payment
 group by
  payment.date) x
group by
  x.date
order by
  x.date