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

Posted by EarthMind on Stack Overflow See other posts from Stack Overflow or by EarthMind
Published on 2010-06-09T13:48:38Z Indexed on 2010/06/09 17:42 UTC
Read the original article Hit count: 183

Filed under:
|
|
|
|

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about postgresql