ON子句中的MySQL未知列 [英] MySQL unknown column in ON clause

问题描述

我有以下MySQL查询:

I have the following MySQL query:

SELECT p.*,
    IF(COUNT(ms.PropertyID) > 0,1,0) AS Contacted,
    pm.MediaID,
    date_format(p.AvailableFrom, '%d %b %Y') AS 'AvailableFrom',
    astext(pg.Geometry) AS Geometry
FROM property p, propertygeometry pg
    JOIN shortlist sl ON sl.PropertyID = p.id AND sl.MemberID = 384216
    LEFT JOIN message ms ON ms.PropertyID = p.id AND ms.SenderID = 384216
    LEFT JOIN property_media pm ON pm.PropertyID = p.id AND pm.IsPrimary = 1
WHERE p.paused = 0
    AND p.PropertyGeometryID = pg.id
GROUP BY p.id

我收到此错误:

#1054-'on子句'中的未知列'p.id'

据我所知，查询看起来是正确的，知道有什么问题吗?

As far as I can see the query looks right, any idea what could be wrong?

推荐答案

请勿混用ANSI-89样式和ANSI-92样式的联接.它们具有不同的优先级，这可能导致混淆错误，而这就是这里发生的情况.您的查询的解释如下:

Don't mix ANSI-89 style and ANSI-92 style joins. They have different precedence which can lead to confusing errors, and that is what has happened here. Your query is being interpreted as follows:

FROM property p, (
    propertygeometry pg
    JOIN shortlist sl ON sl.PropertyID = p.id AND sl.MemberID = 384216
    ...
)

在上面的方法中，首先考虑了使用JOIN关键字的连接，然后才考虑使用逗号样式的连接.此时，尚未声明表p.

In the above, the joins using the JOIN keyword are evaluated first before the comma-style join is even considered. At that point the table p isn't yet declared.

从 MySQL手册:

但是，逗号运算符的优先级小于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.

我建议始终使用ANSI-92样式的连接 ，即使用JOIN关键字:

I'd recommend always using ANSI-92 style joins, i.e. using the JOIN keyword:

SELECT p.*,
    IF(COUNT(ms.PropertyID) > 0,1,0) AS Contacted,
    pm.MediaID,
    date_format(p.AvailableFrom, '%d %b %Y') AS 'AvailableFrom',
    astext(pg.Geometry) AS Geometry
FROM property p
    JOIN propertygeometry pg ON p.PropertyGeometryID = pg.id
    JOIN shortlist sl ON sl.PropertyID = p.id AND sl.MemberID = 384216
    LEFT JOIN message ms ON ms.PropertyID = p.id AND ms.SenderID = 384216
    LEFT JOIN property_media pm ON pm.PropertyID = p.id AND pm.IsPrimary = 1
WHERE p.paused = 0
GROUP BY p.id

相关:

• 为什么不是SQL ANSI-优于ANSI-89的92标准?

这篇关于ON子句中的MySQL未知列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！