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?

score 0 · Accepted Answer · answered Apr 04 '21 at 12:34

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.

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

1 Answers1