sql - ON子句中的MySQL未知列

我有以下 MySQL 查询:

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 clause' 中的未知列 'p.id'

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

最佳答案

不要混合使用 ANSI-89 样式和 ANSI-92 样式连接。它们具有不同的优先级，这可能会导致令人困惑的错误，这就是这里发生的情况。您的查询被解释如下:

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

在上面，首先评估使用 JOIN 关键字的连接，然后再考虑逗号样式的连接。此时表 p 尚未声明。

来自 MySQL manual :

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 关键字:

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