1

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

Table 1:

enter image description here

Table 2:

enter image description here

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:

enter image description here

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!

Stanleyrr
  • 858
  • 3
  • 12
  • 31
  • 1
    `DISTINCT CASE WHEN table1.date is null then table2.date WHEN table2.date is null then table1.date end as date` can be replaced with `coalesce(table1.date, table2.date) AS date` – mike.k Sep 03 '20 at 00:42

1 Answers1

2

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

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thanks, @GMB. The problem is sometimes a Date only exists in one table, not both. In those cases, I still want to put that information into the output table. – Stanleyrr Sep 03 '20 at 00:51
  • @Stanleyrr: that's exactly what the `full join` does. – GMB Sep 03 '20 at 00:56
  • Gotcha. In that case, I will use coalesce(table1.date, table2.date) as date, because sometimes a date will exist in only 1 of the tables. So the coalesce function can ensure I get either value from either table. – Stanleyrr Sep 03 '20 at 01:00
  • 1
    @Stanleyrr: the `full join` allows missing rows from both tables. For example when a row in `t1` has no match in `t2`, it still shows up in the resultset, with values from `t2` having `null` values. `coalesce()` is there to turn `null` values to `0`. – GMB Sep 03 '20 at 01:08
  • Thanks, @GMB. But I can't do "select date from ..." because the query will ask me to specify which table to select the date column from. That's why I had to do "coalesce(t1.date, t2.date) as date". – Stanleyrr Sep 03 '20 at 01:19
  • 1
    @Stanleyrr: in general, you are correct. But here, we use `using ()` in the join condition. This eliminates the ambiguity, as explained in [the documentation](https://www.postgresql.org/docs/current/queries-table-expressions.html): *Furthermore, the output of JOIN USING suppresses redundant columns* – GMB Sep 03 '20 at 01:22