JOIN MySQL上的错误 [英] Error on JOIN mysql

问题描述

您能帮我解决此代码吗?

Can you help me to fix this code?

SELECT Articolisti_Articoli.*
FROM Articolisti_Articoli, Articolisti_Incarichi
LEFT JOIN SitiWeb_Articoli
ON SitiWeb_Articoli.ArticoloID = Articolisti_Articoli.ID
WHERE SitiWeb_Articoli.ArticoloID IS NULL AND Articolisti_Articoli.IncaricoID = Articolisti_Incarichi.ID AND Articolisti_Incarichi.CategoriaID = 15

我收到此错误:

#1054-子句"中的未知列"Articolisti_Articoli.ID"

#1054 - Unknown column 'Articolisti_Articoli.ID' in 'on clause'

但是，如果我尝试使用简单版本:

But if I try the simple version:

SELECT Articolisti_Articoli.*
FROM Articolisti_Articoli
LEFT JOIN SitiWeb_Articoli
ON SitiWeb_Articoli.ArticoloID = Articolisti_Articoli.ID
WHERE SitiWeb_Articoli.ArticoloID IS NULL 

...有效！ :S

...it works! :S

谢谢！

推荐答案

已记录在案:

http://dev.mysql.com/doc/refman/5.0/en/join.html

以前，逗号运算符(，)和JOIN都具有相同的优先级，因此将联接表达式t1，t2 JOIN t3解释为((t1，t2)JOIN t3).现在JOIN具有更高的优先级，因此该表达式被解释为(t1，(t2 JOIN t3)).此更改会影响使用ON子句的语句，因为该子句只能引用联接操作数中的列，并且优先级的更改会更改对这些操作数的解释.

Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.

因此查询将以这种形式失败:

So a query will fail in this form:

SELECT ... FROM A, B JOIN C ON A.x = C.y

最好的解决办法是始终使用JOIN语法:

The best fix is to use JOIN syntax consistently:

SELECT ... FROM A JOIN B ON ... JOIN C ON A.x = C.y

在您的示例中，我将这样写:

And in your example I would write it like this:

SELECT aa.*
FROM Articolisti_Articoli aa
INNER JOIN Articolisti_Incarichi ai ON aa.IncaricoID = ai.ID
LEFT JOIN SitiWeb_Articoli sa ON sa.ArticoloID = aa.ID
WHERE sa.ArticoloID IS NULL AND ai.CategoriaID = 15

这篇关于JOIN MySQL上的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！