I have two tables as follows (in a Postgres 10 database):
id|acreage|date |region 1 |20 |2018-10-18|A 2 |10 |2018-10-07|A 3 |26 |2018-10-18|B 4 |06 |2018-10-07|B
id|acreage|date |region 1 |17 |2018-10-18|A 2 |29 |2018-10-09|A 3 |24 |2018-10-18|B 4 |09 |2018-10-09|B
I want to get the following result:
Maize|Paddy|date |Region 20 |17 |2018-10-18|A Null |29 |2018-10-09|A 10 |Null |2018-10-07|A
Do note that when the dates do not match, I would like to get a Null in the cell.
I've been trying to write a query like this, but I only get the first row, and not the 2nd & 3rd Rows:
Select c1.acreage as "Maize", c2.acreage as "Paddy", c1.date, c1.region FROM Maize c1 FULL OUTER JOIN Paddy c2 ON c1.date =c2.date AND c1.region=c2.region order by c1.date;
Obviously the date in the Join is causing the issue; But I don't know how to remove it, and still get the Matching values in one row.