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
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
You are joining on
marketand 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
COALESCE()in the outer
select 'display' as channel, date, coalesce(market, 'other') as market, revenue, spend