混合显式和隐式连接失败，因为“表中有一个条目……但是不能从查询的这个部分引用它”

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"

错误:对表“i”的from子句项无效引用

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

1号线:…tegory c离开p上的JOIN photos。referencekey =我。关键在哪儿……

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

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

3 个解决方案

#1

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

#2

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.

是一个显式的连接，首先运行。请注意，在这一点上，由于还没有查看表，所以还不能连接表。请注意，我相信MySQL在5.2中修复了这种行为，并且这个查询也将不再在那里工作。

#3

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:

JOIN是在FROM子句中用作from_item的源表的表达式的一部分。您的FROM子句有3个from_item源表:

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:

错误出现在添加到photos from_item的类别的ON join_condition中。您正在引用from_item中不存在的表项。解决方案是将照片连接移动到items from_item中，以便您有以下from_item源表:

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.

遗憾的是，我在文档中找不到一个例子来说明FROM子句中的表和JOIN之间的关系。SELECT大纲显示了这种语法，并且是文档中找到这种区别的最佳来源。注意，JOIN不是来自于FROM的兄弟句，而是FROM子句中的from_item的一部分。因此，如果FROM子句包含一个表列表，那么该列表中的每个表都可以有自己的连接。然后更直观的是，参与联接的每个表都必须包含在一个from_item中。

#1