Select a row from the table and replace the field with one of the other column, if it exists

Question

I am trying to build 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 would like to select all the columns from table A (having the columns: id, name, description) and replace the column "A.name" with the value of the column "B.title", from table B ( with 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 tried using CASE and COALESCE () but could not because of my inexperience with both concepts.

Thanks in advance.

Answer 1

araqnid - , , .

, A , LEFT JOIN. :

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

"max", , . "" , .

, , LEFT JOIN, ( ) JOIN , N ( N ).

, , .

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

, , , - :

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'

- COALESCE():

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'

Answer 4

try it

select A.id,B.title,A.description from TableA as A inner join tableB as B
on A.id=B.id where B.table_A_id = 5 and B.langcode ='nl'