SQL using table from join in subqueries

Question

I am writing a query to be used as databases view, it looks now like this:

SELECT
  contact.*,    
  contact_users.names AS user_names,
  contact_status.status_id AS status_id,
  status_translation.name AS status_name,
  status_translation.lang_id AS lang_id
FROM contacts as contact
LEFT JOIN contact_status AS contact_status ON contact_status.status_id = contact.status
LEFT JOIN contact_status_translation AS status_translation ON status_translation.id = contact.status
LEFT JOIN (
    SELECT
    contacts_users.contact_id,
      string_agg(users.fullname || ', ' || users.id, ' | ') as names  
  FROM v_contacts_users as contacts_users
  LEFT JOIN v_users as users on users.id = contacts_users.user_id
  WHERE users.lang_id = status_translation.lang_id
    GROUP BY contacts_users.contact_id
) AS contact_users ON contact_users.contact_id = contact.id  
WHERE contact.deleted = FALSE

Everything works as expected except the WHERE condition in the last LEFT JOIN - the WHERE users.lang_id = status_translation.lang_id part says that status_translation cannot be referenced in this part of the query? Why is that? I tried to reference this table with various always but the result is still the same.Thing is that v_users is translated as well so I need to have only one result from this table.

Answer 1

Insert LATERAL between LEFT JOIN and the opening parenthesis if you want to reference previous FROM list entries.