Mixing Explicit and Implicit Joins Fails with "There Is an Entry for Table ... But It Cannot Be Referenced from This Part of the Query"

Mixing explicit and implicit joins fails with There is an entry for table ... but it cannot be referenced from this part of the query

The SQL spec states that explicit joins are performed before implicit joins. This is an implicit join:

FROM table1 t1, table2 t2 WHERE t1.id=t2.t1id

This is an explicit join:

FROM table1 t1 JOIN table2 t2 ON (t1.id=t2.t1id)

This code bit:

categories c 
     LEFT JOIN photos p 
        ON p.referencekey = i.key 

is an explicit join and is run first. Note that at this point the table aliased as i hasn't been looked at yet, so it can't be joined yet. Note that MySQL fixed this behaviour in 5.2 I believe, and this query will no longer work there either.

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

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.

postgreSQL There is an entry for table xxx , but it cannot be referenced from this part of the query

The first query is gibberish, the second makes sense but fails because you cannot use a lateral reference to the updated table's columns in the FROM clause.

Try a CTE like this:

WITH addy AS (
   SELECT addy.* FROM
      contacts
      CROSS JOIN LATERAL
      pagc_normalize_address(home_address1 
 || ',' || home_city || ',' || home_state || ',' || ',' || home_zip) AS addy
   WHERE contacts.contact_id = 833826
)
UPDATE contacts
SET (home_house_num, home_predirection, home_street_name, home_street_type,home_postdirection, home_unit_num)
    = (addy.address_alphanumeric,addy.predirabbrev,addy.streetname, addy.streettypeabbrev,addy.postdirabbrev,addy.internal)
FROM addy
WHERE contact_id = 833826;

Mixing implicit and explicit JOINs

It results in an error because according to the SQL standard, the JOIN keyword has higher precedence than the comma. The sticky point is that table aliases are not usable until after the corresponding table has been evaluated in the FROM clause.

So when you reference e1 in your JOIN...ON expression, e1 doesn't exist yet.

Please stand by while I research Hibernate and find out if you can persuade it to use JOIN in all cases.

---

Hmm. Everything at Hibernate.org seems to be redirecting to jboss.org. So no way to read HQL documentation online right now. I'm sure they'll figure out their name serving eventually.

There is an entry for table a , but it cannot be referenced from this part of the query

The problem is in the from clause. In more recent versions of Postgres, you can use:

from test.allotment a cross join lateral
     generate_series(1, a.period_length) n(i)

In older versions, you can include the generate_series() in the select:

from (select a.*, generate_series(1, a.period_length) as i
      from test.allotment a
     ) a

Postgres Invalid Reference to FROM Clause

Commas in Postgres bind more weakly than JOIN expressions; change each comma in the FROM clause to CROSS JOIN. See the note in the relevant section of the docs.

---

Related Topics