MySQL unknown column in ON clause

by

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:

More Related Contents:

  1. MySQL Insert query doesn’t work with WHERE clause
  2. Delete from two tables in one query
  3. PostgreSQL GROUP BY different from MySQL?
  4. GROUP BY behavior when no aggregate functions are present in the SELECT clause
  5. Stored Procedure with optional “WHERE” parameters
  6. MySQL dump by query
  7. SQLite – ORDER BY RAND()
  8. Delete sql rows where IDs do not have a match from another table
  9. How to search multiple columns in MySQL?
  10. In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
  11. What does DELIMITER // do in a Trigger?
  12. Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
  13. MySQL equivalent of DECODE function in Oracle
  14. How do you version your database schema? [closed]
  15. Can a stored procedure/function return a table?
  16. MySQL, Get users rank
  17. Mysql: how to select groups having certain values?
  18. How to fill date gaps in MySQL?
  19. Boolean vs tinyint(1) for boolean values in MySQL
  20. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  21. Function for week of the month in mysql
  22. MySQL’s alternative to T-SQL’s WITH TIES
  23. Find rows that have the same value on a column in MySQL
  24. what is the difference between GROUP BY and ORDER BY in sql
  25. What does collation mean?
  26. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  27. sql pulling a row for next or previous row of a current row
  28. Row Rank in a MySQL View
  29. Select Rows with Maximum Column Value group by Another Column
  30. mysql GROUP_CONCAT duplicates

Leave a Comment