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

Question

SELECT       i.*,        r.name AS roomname,        c.name AS cat,        p.key AS imgkey,        p.extension AS imgext    FROM        items i,        rooms r,        categories c           LEFT JOIN photos p              ON p.referencekey = i.key     WHERE            i.room = r.key        AND r.key = 663308        AND i.sitekey = 32201        AND c.key = i.categorykey 

The above query when executed returns following error.

ERROR: invalid reference to FROM-clause entry for table "i"

LINE 1: ...tegory c LEFT JOIN photos p ON p.referencekey = i.key WHER...

HINT: There is an entry for table "i", but it cannot be referenced from this part of the query.

Answer 1

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.

Answer 2

Move your JOIN statement next to the table you are joining on:

SELECT       i.*,        r.name AS roomname,        c.name AS cat,        p.key AS imgkey,        p.extension AS imgext    FROM        items i          LEFT JOIN photos p              ON p.referencekey = i.key,        rooms r,        categories c     WHERE            i.room = r.key        AND r.key = 663308        AND i.sitekey = 32201        AND c.key = i.categorykey 

The long explanation:

A JOIN is part of an expression that results in a source table, used in the FROM clause as a from_item. Your FROM clause has 3 from_item source tables:

• items
• rooms
• categories joined to photos

The error is in the ON join_condition of your categories joined to photos from_item. You are referencing a table, items, that does not exist in the from_item. The solution is to move the photos join into the items from_item, so that you have the following from_item source tables:

• items joined to photos
• rooms
• categories

Sadly, I can't find an example in the documentation that makes clear this relationship between a table in the FROM clause and a JOIN. The SELECT Synopsis shows this syntax and is the best source in the documentation to find this distinction. Notice that a JOIN is not a sibling clause to FROM, but actually part of a from_item within the FROM clause. Thus, if your FROM clause consists of a list of tables, each table in that list can have its own joins. Then it becomes more intuitive that each table involved in a join must be included in a single from_item.