SQL 混合显式和隐式连接失败，并显示“表有一个条目......但它不能从查询的这一部分引用”

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

提问by Umair A.

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.

错误：对表“i”的 FROM 子句条目的引用无效

第 1 行：...tegory c LEFT JOIN photos p ON p.referencekey = i.key WHER...

提示：表“i”有一个条目，但不能从查询的这一部分引用。

采纳答案by DRapp

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

由于您的 Items.Room = Room.Key，我会将其作为 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 

回答by Scott Marlowe

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

SQL 规范声明显式连接在隐式连接之前执行。这是一个隐式连接：

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.

是显式连接并且首先运行。请注意，此时别名为 i 的表还没有被查看，因此还不能加入。请注意，我相信 MySQL 在 5.2 中修复了此行为，并且此查询在那里也不再有效。

回答by gilly3

Move your JOINstatement next to the table you are joining on:

将您的JOIN陈述移到您要加入的表格旁边：

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 JOINis part of an expression that results in a source table, used in the FROMclause as a from_item. Your FROMclause has 3 from_itemsource tables:

AJOIN是生成源表的表达式的一部分，在FROM子句中用作from_item。您的FROM子句有 3 个from_item源表：

• items
• rooms
• categoriesjoined to photos

• items
• rooms
• categories加入 photos

The error is in the ONjoin_conditionof your categoriesjoined to photosfrom_item. You are referencing a table, items, that does not exist in the from_item. The solution is to move the photosjoin into the itemsfrom_item, so that you have the following from_itemsource tables:

错误出在您加入from_item的ONjoin_condition中。您正在引用from_item中不存在的表。解决方案是将联接移动到from_item 中，以便您拥有以下from_item源表：categoriesphotositemsphotositems

• itemsjoined to photos
• rooms
• categories

• items加入 photos
• rooms
• categories

Sadly, I can't find an example in the documentation that makes clear this relationship between a table in the FROMclause and a JOIN. The SELECTSynopsisshows this syntax and is the best source in the documentation to find this distinction. Notice that a JOINis not a sibling clause to FROM, but actually part of a from_itemwithin the FROMclause. Thus, if your FROMclause 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.

遗憾的是，我在文档中找不到一个例子来明确FROM子句中的表和JOIN. 该SELECT概要显示了这种语法和在文档中的最佳来源找到这种区别。请注意， aJOIN不是 的同级子句FROM，而是该子句中from_item 的实际一部分FROM。因此，如果您的FROM子句包含一个表列表，那么该列表中的每个表都可以有自己的连接。然后，连接中涉及的每个表都必须包含在单个from_item 中变得更加直观。