WHERE inside COALESCE-CodePudding

I'd like to return all topics_i18n.content_i18n which match both id and language_code but if the id exists but has no corresponding row in the other table that holds the language_code then return topics.content.

I've tried with 'COALESCE'

  SELECT
    topics.id,
    COALESCE (topics_i18n.content_i18n, topics.content) content,
    topics_i18n.language_code
  FROM topics
  LEFT JOIN topics_i18n
  ON topics.id = topics_i18n.topic_id
  WHERE section_id = $1 AND topics_i18n.language_code = $2
  ORDER BY position ASC

However this does not work, I feel like I need a WHERE inside the COALESCE?

  SELECT
    topics.id,
    COALESCE (topics_i18n.content_i18n WHERE topics_i18n.language_code = $2, topics.content) content,
    topics_i18n.language_code
  FROM topics
  LEFT JOIN topics_i18n
  ON topics.id = topics_i18n.topic_id
  WHERE section_id = $1 
  ORDER BY position ASC

Any suggestions on how I can resolve this?

CodePudding user response：

Move the language subexpression from the where clause into the on condition and it should work fine. I guess that this is actually the business logic. Here is your query with the modification.

  SELECT
    topics.id,
    COALESCE (topics_i18n.content_i18n, topics.content) content,
    topics_i18n.language_code
  FROM topics
  LEFT OUTER JOIN topics_i18n
    ON topics.id = topics_i18n.topic_id 
   AND topics_i18n.language_code = $2
  WHERE topics.section_id = $1
  ORDER BY position ASC;