How to fix full outer join of two tables when not all values exist in both tables

I have two tables I want to join. One of the variables I'm joining is the country name. However, one of the tables has values, while the other table doesn't have all the values. This produces blank rows without all the data.

This is what I've currently trying

select 'display' as channel, date, market, revenue, spend
from (
  select trim(market) as market, date, revenue
  from table_a
) ABC
FULL JOIN (
  select 
    case geosegmentation_countries
      when 'united kingdom' then 'UK'
      when 'germany' then 'DE'
      when 'france' then 'FR'
      when 'italy' then 'IT'
      when 'spain' then 'ES'
      else 'Other'
    end as market, 
    date, 
    spend
  from table_b
) OM on ABC.market = OM.market and ABC.date = OM.Date

The ABC table doesn't have the "other" value in markets. This is giving me this output, with blank dates and markets.  This is giving me this output, with blank dates and markets

How can I fix this join so that the other market data has the correct dates coming through, with the 'other' data being filled in the market column?

1 answer

  • answered 2019-10-10 10:41 Gordon Linoff

    You are joining on market and returning it. I'm not sure why you are using a full join (your explanation suggests a left join, but it is not clear what table has all the rows you want).

    That said, to get 'Other', use COALESCE() in the outer SELECT:

    select 'display' as channel, date,
           coalesce(market, 'other') as market,
           revenue, spend