sql - Three columns in two tables to four columns in one table -

i'm trying combine 2 tables, each has 3 columns , 2 of common. income table has location, date , amt_recd, expense table has location, date , amt_spent. want combine these 2 tables there 1 table 4 columns, , common dates combined 1 row. i've tried this:

select location, date, amt_recd, null amt_spent income  union  select location, date, null amt_recd, amt_spent expense 

and gets me 1 step away, since not combine dates 1 row, has 2 rows amt_recd null in 1 , amt_spent null in other. better way construct result more condensed? i've tried various joins instead of union, , haven't been able result i'm looking for.

i basing answer on line original post:

i want combine these 2 tables there 1 table 4 columns, , common dates combined 1 row.

the second half of suggests need not join, aggregate.

select    coalesce(i.location, e.location) location , coalesce(i.[date], e.[date]) [date] , sum(i.amt_recd) amt_recd , sum(e.amt_spent) amt_spent income full outer join expense e   on i.location = e.location   , i.date = e.date group    coalesce(i.location, e.location) , coalesce(i.[date], e.[date])  

this give 1 row per unique combination of location & date.

if need further reduce results 1 row per date, need specify rules location show when there more 1 location on given date.