sql - MySQL unknown column in ON clause

Question

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

And I'm getting this error:

#1054 - Unknown column 'p.id' in 'on clause'

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

Answer 1

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
    ...
)

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.

From the 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.

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

Related:

• Why isn't SQL ANSI-92 standard better adopted over ANSI-89?