MySQL“子句中的未知列" [英] MySQL "Unknown Column in On Clause"

问题描述

我有以下MySQL查询:

I have the following MySQL query:

SELECT posts.id,  posts.name,  LEFT(posts.content, 400),  posts.author,  posts.date, users.display_name,
  GROUP_CONCAT(tags.tag ORDER BY tag_linking.pid ASC SEPARATOR ",") update_tags
FROM posts, tag_linking, tags
INNER JOIN `users`
ON posts.author=users.id;
WHERE tag_linking.pid = posts.id 
  AND tags.id = tag_linking.tid 
ORDER BY posts.date DESC

您可以看到哪个连接了三个表，等等.无论如何，问题在于它给出了一个错误:

Which, was you can see, connects three tables etc. etc. Anyway, the problem is that it gives an error:

ERROR CODE:
SQL Error (1054): Unknown column 'posts.author' in 'on clause'

即使在另一个页面上使用的这种更简单的查询也可以:

even though this simpler query used on another page works:

SELECT posts.id,  posts.name,  LEFT(posts.content, 400),  posts.author,  posts.date, users.display_name FROM `posts`
INNER JOIN `users`
ON posts.author=users.id

有人对这种情况为什么发生有想法吗?感谢您的帮助.

Does anyone have thoughts as to why this is occuring? Thanks for your help.

推荐答案

因为您的混合联接语法

From Mysql ^[docs]

但是，逗号运算符的优先级小于INNER的优先级 JOIN，CROSS JOIN，LEFT JOIN等. 如果您将逗号连接与 存在联接条件时的其他联接类型，则错误 可能会出现"on子句"中的未知列"col_name"的形式.信息 有关如何解决此问题的内容将在本节的稍后部分给出.

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.

解决方案是:

要允许处理联接，请将前两个表分组 显式地带有括号，以便ON子句的操作数为 (t1，t2)和t3:

To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

SELECT * FROM(t1，t2)JOIN t3 ON(t1.i1 = t3.i3);

或者，避免使用逗号运算符并使用JOIN 相反:

Alternatively, avoid the use of the comma operator and use JOIN instead:

选择* FROM t1 JOIN t2 JOIN t3 ON(t1.i1 = t3.i3);

这篇关于MySQL“子句中的未知列"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！