MYSQL未知子句在下一个连接中连接列 [英] MYSQL unknown clause join column in next join

问题描述

我有以下查询：

SELECT * FROM questions 
LEFT JOIN answers ON (questions.id = answers.id AND (connections.username = answers.username OR connections.username = 'bob' OR answers.username IS NULL))
LEFT JOIN connections ON connections.username1 = 'mikha' AND
                         (connections.username2 = answers.username) 
LEFT JOIN answers answers2 ON (questions.id = answers2.id)
WHERE (answers.id <> answers2.id)

我收到以下错误：

`Unknown connections.username in ON clause`

我在第一个连接中定义了一些条件，得到不符合这些条件的休息。这就是为什么我使用这部分 answers.id AND answers2.id 。

I define some conditions in the first join and want to get the rest that don't match these conditions. That's why I use this part answers.id AND answers2.id. To get IDs that don't match the conditions in the first left join.

感谢

推荐答案

尝试这样，我没有模式来测试自己，但我觉得它应该工作（或类似的东西）

Try it like this, I have no schema to test it myself but I feel like it should work(or something like this)

SELECT * FROM questions, connections
LEFT JOIN answers ON (questions.id = answers.id AND
                     connections.username2 = answers.username)
where connections.username1 = 'mikha';

最终如此

SELECT * FROM questions
LEFT JOIN answers ON (questions.id = answers.id)
LEFT JOIN connections ON (connections.username2 = answers.username)
where connections.username1 = 'mikha';

编辑：
我在文档中找到了这个

I found this in documentation

示例：

Example:

CREATE TABLE t1（i1 INT）;
CREATE TABLE t2（i2 INT）;
CREATE TABLE t3（i3 INT）;
SELECT * FROM t1 JOIN t2 ON（i1 = i3）JOIN t3;

CREATE TABLE t1 (i1 INT); CREATE TABLE t2 (i2 INT); CREATE TABLE t3 (i3 INT); SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;

以前，SELECT语句是合法的。现在语句失败，并且在'on clause'错误中出现未知>列'i3'，因为i3是t3中的列，它不是ON子句的操作数。该语句应改写为：

Previously, the SELECT statement was legal. Now the statement fails with an Unknown >column 'i3' in 'on clause' error because i3 is a column in t3, which is not an operand of >the ON clause. The statement should be rewritten as follows:

SELECT * FROM t1 JOIN t2 JOIN t3 ON（i1 = i3）;

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

因此，对于您的情况，可能是

So for Your case it may be

SELECT * FROM questions  
LEFT JOIN connections
LEFT JOIN answers ON (connections.username1 = 'mikha' AND questions.id = answers.id AND
                 connections.username2 = answers.username)

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