Postgresql – How to do a Join with optionally matching columns

join;postgresqlquery

I have two tables as follows (in a Postgres 10 database):

Maize:

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

Paddy:

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.

Best Answer

SELECT c1.acreage Maize, 
       c2.acreage Paddy, 
       COALESCE(c1."date",c2."date") "date", 
       COALESCE(c1.region, c2.region) region 
FROM Maize c1 
FULL OUTER JOIN Paddy c2 
    ON c1."date"=c2."date" AND c1.region=c2.region 
ORDER BY "date";

fiddle

Related Question