SQL连接，“有一个表条目，但不能被引用". [英] SQL joins, "there is an entry for table but it cannot be referenced"

问题描述

我有一个失败的SQL查询，给我错误:

I have a SQL query which is failing, giving me the error:

有一个表条目，但不能在查询的这一部分中引用它"

"There is an entry for table but it cannot be referenced from this part of the query"

通过查询，我需要全部3个表，但只有行程和船具有匹配的ID才能加入.测试是一个shapefile，我需要在其上执行postGIS功能，但它与其他两个ID没有相似的列ID.

Going through the query, I need both all 3 tables, but only trips and boats have a matching ID to join on. Test is a shapefile which I need to perform a postGIS function on but it shares no similar column id's to the other two.

 select trips.*
 from trips, test
 inner join boats on boats.id = trips.boat_id
 where st_intersects(trips.geom, test.geom) and
 boats.uid = 44

我认为这与join语句有关，但我不太了解.我对这里发生的事情的解释非常感兴趣.

I think it has something to do with the join statement but I don't really understand what. I'd be very interested in an explanation of what is happening here.

推荐答案

简单规则:从不在FROM子句中使用逗号. 始终使用显式的JOIN语法. . .即使这意味着写出CROSS JOIN.

Simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax . . . even if that means writing out CROSS JOIN.

如评论中所述，编写查询的正确方法是:

As mentioned in the comments, the right way to write the query is:

 select trips.*
 from trips inner join
      test
      on st_intersects(trips.geom, test.geom) inner join
      boats
      on boats.id = trips.boat_id
 where boats.uid = 44;

但是，问题是为什么这不起作用:

However, the question is why this doesn't work:

from trips,
     test inner join
     boats
     on boats.id = trips.boat_id

首先，我想指出，此语法的失败在MySQL 文档，而不是Postgres文档中的

First, I want to note that the failure of this syntax is better described in the MySQL documentation than in the Postgres documentation:

但是，逗号运算符的优先级小于INNER的优先级 JOIN，CROSS JOIN，LEFT JOIN等.如果您混合使用逗号联接 存在联接条件时的其他联接类型，则错误 可能会出现"on子句"中的未知列"col_name".信息 有关如何解决此问题的内容将在本节的稍后部分给出.

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.

我认为一种简单的描述方式是JOIN是一个运算符，它对作为其参数的表/表达式进行操作.逗号分隔参数.因此，第一个表根本无法识别，因为逗号会阻止"它.

I think a simpler way of describing this is that JOIN is an operator that acts on the tables/expressions that are its arguments. The comma separates the arguments. So, the first table simply isn't recognized because the comma "blocks" it.

我认为您可以使用括号解决此问题:

I think you can get around this using parentheses:

from (trips, test inner) join
     boats
     on boats.id = trips.boat_id

您绝对可以使用CROSS JOIN来解决它:

You can definitely get around it using CROSS JOIN:

from trips cross join
     test inner join
     boats
     on boats.id = trips.boat_id

但是为什么要打扰呢?有写查询的正确方法.

But why bother? There is a correct way to write the query.

这篇关于SQL连接，“有一个表条目，但不能被引用".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！