MYSQL Join Union Group By - two tables advice

[eluser]nevsie[/eluser]
A brief synopsis...
I have two tables income and expenses. Both have many columns, but the ones i am interested in are date and amount... I want to output monthly total amounts for all entries in income and expenses.

Individually i can run something like:

Code:

SELECT sum(income.amount), sum(expense.amount), month(payments.incdate)
  FROM income
  JOIN expense ON month(expense.expdate) = month(income.incdate)
  GROUP BY month(income.incdate)

Now in the example above this works fairly well but as it is a JOIN it only returns results where income and expense both have entries for that month. If i do a LEFT JOIN it brings back the extra rows from income table, and visa versa RIGHT JOIN extra rows from expense table - ideally FULL JOIN would bring back both sides, but FULL JOIN is not supported by MYSQL...

Therefore i tried doing two SELECT's like the above one for LEFT and one for RIGHT and then UNION to bring them together. But union caused no end of problems for me! Mostly messing up and mixing up the two sum(value) amounts and mixing the adding etc.

I have also tried UNIONing two simpler queries and place a NULL value in the select hoping the union would track the results columns, etc, but this would not bring the results together it just created more rows in the result.

Yes i could do this with multiple queries, and run some for/foreach loops to join the results, but i was just trying to be cleaner and cleverer and do it properly (learn at the same time).

Any ideas appreciated - especially if i am overcomplicating this! Thanks, N