How to JOIN and get data from either table based on specific logics?

Question

Let's say I have 2 tables as shown below:

Table 1:

Table 2:

I want to join the 2 tables together so that the output table will have a "date" column, a "hrs_billed_v1" column from table1, and a "hrs_billed_v2" column from table2. Sometimes a date only exists in one of the tables, and sometimes a date exists in both tables. If a date exists in both table1 and table2, then I want to allocate the hrs_billed_v1 from table1 and hrs_billed_v2 from table2 to the output table.

So the ideal result will look like this:

I've tried "FULL OUTPUT JOIN" but it returned some null values for "date" in the output table. Below is the query I wrote:

SELECT 
DISTINCT CASE WHEN table1.date is null then table2.date WHEN table2.date is null then table1.date end as date, 
CASE WHEN table1.hrs_billed_v1 is null then 0 else table1.hrs_billed_v1 END AS hrs_billed_v1, 
CASE WHEN table2.hrs_billed_v2 is null then 0 else table2.hrs_billed_v2 END AS hrs_billed_v2
FROM table1         
FULL OUTER JOIN table2 ON table1.common = table2.common

Note that the "common" column where I use to join table1 and table2 on is just a constant string that exists in both tables.

Any advice would be greatly appreciated!

Answer 1

A full join is indeed what you want. I think that would be:

select 
    common,
    date,
    coalesce(t1.hrs_billed_v1, 0) as hrs_billed_v1,
    coalesce(t2.hrs_billed_v2, 0) as hrs_billed_v2
from table1 t1
full join table2 t2 using (common, date)

Rationale:

• you don't show what common is; your data indicates that you want to match rows of the same date - so I put both in the join condition; you might need to adapat that

• there should really be no need for distinct

• coalesce() is much shorter than the case expressions

• using () is handy to express the join condition when the columns to match have the same name in both tables