Column empty or null then fetch value from other table

Question

I have a 3 tables in Postgresql. I have a Table C wherein I want the results from Table A. If the values for region is null/blank from Table A then I need the region value from Table B. How do I join these in Postgresql? Or how can this be done in Talend?

Answer 1

How about something like this?

SELECT * FROM (
  SELECT COALESCE(ta.country, tb.country) as country, tc.region 
  FROM tb
  FULL OUTER JOIN (
    SELECT country, region FROM tablec
  ) tc ON tc.country = tb.country
  FULL OUTER JOIN (
    SELECT country, region FROM tablea
  ) ta ON ta.country = tb.country
) WHERE country IS NOT NULL AND region IS NOT NULL

It will zip all tables together and select the preferred rows then throw away to empty ones. COALESCE will select the first non null value from its arguments.