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?

Namesake

I'm sure you have checked, but you do have a column id on table property that is aliased as p? – Stirpiculture

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?

Bermudez

No such terminology as "implicit" or "explicit" with respect to JOIN syntax. – Merwin

Answer 2

As stated before there is a precedence issue using joins via the comma operator where the LEFT JOIN will be executed and so references to table aliases won't exist at that time. Though you can implicitly tell MySQL to use a JOIN via that statement you may also tell MySQL to evaluate the comma joined tables first, then execute left join thusly:

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

Notice the comma separated tables are contained within parenthesis (). The table aliases and columns will now be available to your other JOINs.

Trigg

Answer 3

I bumped into this error unknown column, the diff is the query is built thru HQL inside session.executeQuery("select id, name, sum(paid), custType from cust group by brand") that's why having to manually type inner join or join keyword is not an option as the hql is the one generating it. it produces a query sumthing like this:

select cust_id, name, sum(paid), c.custTypeId
from customer c, custType ct
on c.custTypeId  = ct.custTypeId 

it says "unknown c.custTypeId" column when I am 101% sure it bears that column.

My classes/relations:

Customer {
Integer custId
CustomerType custType
}

CustomerType{
 Integer custTypeId
string code
}

the problem lies in the comma in "from customer, custType" line. it should be with the word JOIN as the answer stated above. but since it is HQL and is being generated, I can't do that. What I did is modified by query and instead of typing select custType, I typed select custType.id, custType.code

I know it's basic but for first timers like me, it was a struggle.

Weigela

Answer 4

Just in case someone else shoots themselves in the foot like this.

I was new to MySQL Workbench and testing a new schema, I however didn't think to select to add the DROP commands while pushing to the server for testing.

Which meant that when I tweaked my tables and updated the view, I kept getting these weird errors.

Long story short make sure you are actually updating the table schema on the server on every push while in the early testing phase, otherwise your new view may be failing because of the old tables on the server.

Trinatte

Answer 5

If this helps someone (and a note to future myself), I was getting this error when trying to execute the following queries in MariaDB:

SELECT a.name, b.name
FROM `cities` as a
INNER JOIN `countries` as b 
ON `a.country_id` = `b`.`id`;

whereas I should have written it like:

SELECT a.name, b.name
FROM `cities` as a
INNER JOIN `countries` as b 
ON `a`.`country_id` = `b`.`id`;

I'll leave it to the reader to spot the difference as an exercise. :)

Doubs

a semantical error might result in all kinds of weird behavior including but not exclusively syntax errors. in this case it might've helped you but is unrelated to the question. – Nostoc