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.

Change the where to and in the inline view and you should be fine. The issue is when using outer join, you can only have a where clause on the left side of the left join, or the null records generated by the left join are omitted; as NULL is <> to your text. (null can't be compared this way!) In essence the left join becomes an inner join. By moving the where to the join criteria, the problem goes away as the filter is applied before the join occurs, thus allowing the null records created from the join to remain. — xQbert
– xQbert, Commented May 23, 2017 at 12:41
Use lateral keyword if you want to use some values from out side of subquery. Left join lateral ( subquery ). Lateral keyword is present from 9.3 onwards postgres. — Fahad Anjum
– Fahad Anjum, Commented May 23, 2017 at 12:44

Laurenz Albe · Accepted Answer · 2017-05-23 12:44:02Z

1

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

answered May 23, 2017 at 12:44

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL using table from join in subqueries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related