select row from table and substitute a field with one from another column if it exists

Question

I'm trying construct a PostgreSQL query that does the following but so far my efforts have been in vain.

Problem: There are two tables: A and B. I'd like to select all columns from table A (having columns: id, name, description) and substitute the "A.name" column with the value of the column "B.title" from table B (having columns: id, table_A_id title, langcode) where B.table_A_id is 5 and B.langcode is "nl" (if there are any rows).

My attempts:

SELECT A.name,
 case when exists(select title from B where table_A_id = 5 and langcode= 'nl')
 then B.title
 else A.name
END
FROM A, B
WHERE A.id = 5 and B.table_A_id = 5 and B.langcode = 'nl'

-- second try:
SELECT COALESCE(B.title, A.name) as name
from A, B
where A.id = 5 and B.table_A_id = 5 and exists(select title from B where table_A_id = 5 and langcode= 'nl')

I've tried using a CASE and COALESCE() but failed due to my inexperience with both concepts.

Thanks in advance.

Answer 1

araqnid's is the answer you are looking for, I bet.

But if you want to enforce that no more than one row is returned for each original matching A row, you might prefer to do a subselect instead of a LEFT JOIN. For example:

SELECT A.id, COALESCE(
  ( SELECT max(B.title) FROM B WHERE
    langcode = 'nl' AND B.table_a_id = A.id), A.name ) as name
FROM  A
WHERE A.id = 5

I use "max" here to select an arbitrary value, in the event there is more than one. You can use "min" or whatever you consider appropiate in your case.

Perhaps this is more easy to understand than the LEFT JOIN, but (apart from the two being not exactly equivalent) a JOIN will perform better than N subselects (much better is N is large).

Anyway, from a learning point of view, it's good to understand both.

Answer 2

select A.id, coalesce(B.title, A.name)
from TableA AS A
     left join (select table_a_id, title from TableB where langcode = 'nl') AS B
       on B.table_a_id = A.id
WHERE A.id = 5

Answer 3

Ok, I'm not sure how your tables have to be joined, but something like this should do the job:

SELECT            yourcolumnlist,
                  CASE WHEN A.name IS NULL THEN B.title ELSE A.name END
FROM              TableA AS A
INNER JOIN        TableB AS B
ON                A.id = B.table_A_id
WHERE             B.table_A_id = 5
AND               B.langcode = 'nl'

Another way to do it would be to use the COALESCE() function:

SELECT            yourcolumnlist,
                  COALESCE(A.name, B.title)
FROM              TableA AS A
INNER JOIN        TableB AS B
ON                A.id = B.table_A_id
WHERE             B.table_A_id = 5
AND               B.langcode = 'nl'