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 roomna开发者_运维技巧me, 
      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.

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.

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.

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