Invalid reference to FROM-clause entry for table "t1" in postgres query

Question

I'm trying to perform a query like this...

select * from table1 as t1
left join (
    select * from table2 as t2
    where t2.id = t1.t2_id
) as tt2
where tt2.value = 'SOME VALUE'

& I'm getting error like this...

ERROR: invalid reference to FROM-clause entry for table "t1"
  Hint: There is an entry for table "t1", but it cannot be referenced from this part of the query.

That error message totally make sense, but I just wanna know if it's possible to match the values of 't1' with 't2' maintaining the same structure?

Answer 1

You can use a lateral join instead if you really want a correlated subquery:

select *
from table1 t1 left join lateral
     (select * 
      from table2 t2
      where t2.id = t1.t2_id
     ) tt2
     on 1=1
where tt2.value = 'SOME VALUE';

Note that that the where clause undoes the left join. I suspect you really want a simple left join:

select *
from table1 t1 left join
     table2 t2
     on t2.id = t1.t2_id and t2.value = 'SOME VALUE';

This returns rows in t1 that have no matching row in t2 with 'SOME VALUE'. If you don't want them, change the left join to an inner join.