MYSQL unknown clause join column in next join

I have the following query:

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)

I get the following error:

`Unknown connections.username in ON clause`

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.

Thanks

Solution

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';

eventually like this

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

EDIT: 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;

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);

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)