blocks|key|3013461|text|不要混用ANSI-89样式和ANSI-92样式的连接。它们具有不同的优先级，这可能会导致混淆的错误，这就是这里发生的情况。您的查询将按如下方式解释：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3013462|FROM+property+p,+(
++++propertygeometry+pg
++++JOIN+shortlist+sl+ON+sl.PropertyID+=+p.id+AND+sl.MemberID+=+384216
++++...
)|code-block|syntax|javascript|3013463|在上面的代码中，使用JOIN关键字的连接在考虑逗号样式连接之前首先被评估。此时，表p还没有声明。|offset|length|style|CODE|3013464|从MySQL+manual|3013465|3013466|但是，逗号运算符的优先级低于内部联接、交叉联接、左联接等。如果在存在联接条件时将逗号联接与其他联接类型混合使用，则可能会出现'on子句‘中的形式为Unknown列'col_name’的错误。有关处理此问题的信息将在本节的后面部分提供。|blockquote|BOLD|3013467|3013468|3013469|我建议始终使用ANSI-92样式的连接，即使用JOIN关键字：|3013470|SELECT+p.*,
++++IF(COUNT(ms.PropertyID)+>+0,1,0)+AS+Contacted,
++++pm.MediaID,
++++date_format(p.AvailableFrom,+'%25d+%25b+%25Y')+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|3013471|相关信息：|3013472|3013473|Why+isn't+SQL+ANSI-92+standard+better+adopted+over+ANSI-89?|unordered-list-item|3013474|3013475|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89|1^0|0|0|15|1|0|1|C|0|0|0|21|18|0|0|0|0|V|0|0|0|5|0|0|0|1N|0|1N|1|0|0^^$0|@$1|2|3|4|5|6|7|1F|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1G|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1H|8|@$I|1I|J|1J|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|1K|8|@]|9|@$I|1L|J|1M|1|1N]]|A|$]]|$1|O|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]|$1|P|3|Q|5|R|7|1P|8|@$I|1Q|J|1R|K|S]]|9|@]|A|$]]|$1|T|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1T|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|1U|8|@$I|1V|J|1W|K|S]]|9|@]|A|$]]|$1|X|3|Y|5|D|7|1X|8|@]|9|@]|A|$E|F]]|$1|Z|3|10|5|6|7|1Y|8|@$I|1Z|J|20|K|S]]|9|@]|A|$]]|$1|11|3|-4|5|6|7|21|8|@]|9|@]|A|$]]|$1|12|3|13|5|14|7|22|8|@$I|23|J|24|K|S]]|9|@$I|25|J|26|1|27]]|A|$]]|$1|15|3|-4|5|6|7|28|8|@]|9|@]|A|$]]|$1|16|3|-4|5|6|7|29|8|@]|9|@]|A|$]]]|17|$18|$5|19|1A|1B|A|$1C|1D]]|1E|$5|19|1A|1B|A|$1C|1D]]]]

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:

<pre><code>FROM property p, (
 propertygeometry pg
 JOIN shortlist sl ON sl.PropertyID = p.id AND sl.MemberID = 384216
 ...
)
</code></pre>

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 <code>p</code> isn't yet declared.

From the <a href="https://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89">MySQL manual</a>:

<blockquote>
 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. 
</blockquote>

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

<pre><code>SELECT p.*,
 IF(COUNT(ms.PropertyID) &gt; 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
</code></pre>

Related:

<ul>
<li><a href="https://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89">Why isn't SQL ANSI-92 standard better adopted over ANSI-89?</a></li>
</ul>

blocks|key|292397|text|我碰到了这个错误的未知列，不同之处在于查询是通过session.executeQuery内部的hql构建的(“select+id，name，sum(paid)，custType+from+cust+group+by+brand")，这就是为什么必须手动键入inner+join或join关键字不是一个选项，因为生成它的是HQL。它会生成如下的查询求和：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|292398|select+cust_id,+name,+sum(paid),+c.custTypeId
from+customer+c,+custType+ct
on+c.custTypeId++=+ct.custTypeId+|code-block|syntax|javascript|292399|当我101%25确定它包含这一列时，它会显示“未知c.custTypeId”列。|292400|我的类/关系：|292401|Customer+{
Integer+custId
CustomerType+custType
}

CustomerType{
+Integer+custTypeId
string+code
}|292402|问题出在"from+customer，custType“行中的逗号。它应该使用单词JOIN作为上面提到的答案。但是因为它是HQL并且正在生成，所以我不能这样做。我所做的是通过查询进行修改，我输入的不是select+custType，而是select+custType.id,+custType.code|offset|length|style|CODE|292403|我知道这很基本，但对于像我这样的初学者来说，这是一场斗争。|292404|entityMap^0|0|0|0|0|0|3A|X|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|11|8|@$O|12|P|13|Q|R]]|9|@]|A|$]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

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:

<pre><code>select cust_id, name, sum(paid), c.custTypeId
from customer c, custType ct
on c.custTypeId = ct.custTypeId 
</code></pre>

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

My classes/relations:

<pre><code>Customer {
Integer custId
CustomerType custType
}

CustomerType{
 Integer custTypeId
string code
}
</code></pre>

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 <code>select custType.id, custType.code</code>

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

blocks|key|3227621|text|如前所述，通过逗号操作符使用连接存在优先级问题，此时将执行左连接，因此将不存在对表别名的引用。虽然您可以通过该语句隐式地告诉MySQL使用连接，但您也可以告诉MySQL首先计算逗号连接表，然后执行left+JOIN：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3227622|SELECT+p.*,
IF(COUNT(ms.PropertyID)+>+0,1,0)+AS+Contacted,
pm.MediaID,
date_format(p.AvailableFrom,+'%25d+%25b+%25Y')+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|code-block|syntax|javascript|3227623|请注意，逗号分隔的表包含在括号()中。现在，表别名和列将可用于其他联接。|3227624|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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:

<pre><code>SELECT p.*,
IF(COUNT(ms.PropertyID) &gt; 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
</code></pre>

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

I have the following MySQL query:

<pre><code>SELECT p.*,
 IF(COUNT(ms.PropertyID) &gt; 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
</code></pre>

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?

MySQL unknown column in ON clause

MySQL

我有以下MySQL查询：SELECT p.*,    IF(COUNT(ms.PropertyID) > 0,1,0) AS Contacted,    pm.MediaID,    date_format(p.AvailableFrom, '%d %b %Y') AS 'AvailableFrom',    aste...

问ON子句中的MySQL未知列
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问ON子句中的MySQL未知列EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问ON子句中的MySQL未知列
EN