entry cannot be referenced in this part of the query (subquery) Error

Question

I'm getting the following error on my query:

here is an entry for table "table1", but it cannot be referenced from this part of the query.

This is my query:

SELECT id 
FROM property_import_image_results table1
  LEFT JOIN (
    SELECT created_at 
    FROM property_import_image_results
    WHERE external_url = table1.external_url
    ORDER BY created_at DESC NULLS LAST 
    LIMIT 1
  ) as table2 ON (pimr.created_at = table2.created_at)
WHERE table2.created_at is NULL

Answer 1

You need a lateral join to be able to reference the outer table in the sub-select for the join.

You are also referencing an alias pimr in the join condition, which isn't available anywhere in the query. So you need to change that to table1 in the join condition.

You should also given the table in the inner query an alias to avoid confusion:

SELECT id 
FROM property_import_image_results table1
  LEFT JOIN LATERAL (
    SELECT p2.created_at 
    FROM property_import_image_results p2
    WHERE p2.external_url = table1.external_url
    ORDER BY p2.created_at DESC NULLS LAST 
    LIMIT 1
  ) as table2 ON (table1.created_at = table2.created_at)
WHERE table2.created_at is NULL

Edit

This kind of query can also be solved using window functions:

select id
from (
  select id,  
         max(created_at) over (partition by external_url) as max_created
  FROM property_import_image_results
) t
where created_at <> max_created;

This might be faster than aggregating and joining as you do. But it's hard to tell. The lateral joins are quite efficient as well. It has the advantage that you can add any column you like to the result because no grouping is required.