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

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.

Solution 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.

Solution 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.

Solution 3:

Since your Items.Room = the Rooms.Key, I would just have that as the where..

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 
         JOIN rooms r
            on i.room = r.key
         JOIN categories c 
            on i.categorykey = c.key
   WHERE 
          i.sitekey = 32201 
      AND i.room = 663308