MYSQL unknown clause join column in next join

Question

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

Because there is no column username in your table answer. Am I missing something? — sdespont, Dec 09 '12 at 01:11
actually there is username column..the problem is `answers.username` is in the first part of the query before joining the table answers in the second part. So, it is considered undefined — Michael Samuel, Dec 09 '12 at 01:15
@MichaelSamuel just deleted my answer. In your case you can't. The best possible way is to filter the condition on the `WHERE` clause. — John Woo, Dec 09 '12 at 01:19
It seems I need the full scheme to explain more. I'm fed up with this query so I will post the full scheme and explain everything later. Thanks :) — Michael Samuel, Dec 09 '12 at 02:19

Gustek · Accepted Answer · 2012-12-09T01:55:56.630

1

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)

edited Dec 09 '12 at 01:55

answered Dec 09 '12 at 01:26

Gustek

3,680
2
22
36

I know this one but the problem I need it to be done the way not with where because the query is much more complex than that. so, i need it to be done this way. Thanks :) – Michael Samuel Dec 09 '12 at 01:41
if You could post schema and some test data with example of expected result it would be easier to find good alternative. – Gustek Dec 09 '12 at 01:45
Tried it but I get this error: `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 4` – Michael Samuel Dec 09 '12 at 02:09
I edited my question to explain more why I need to define conditions inside the join not in the where – Michael Samuel Dec 09 '12 at 02:11

score 1 · Answer 2 · answered Dec 09 '12 at 02:12

Effectively if your limiting the results of answers by the results of connections you are doing an inner join not two left joins because one row cannot return with out results from the other because null does not equal null. Given this to be true you can use the following because I have no clue what your schema is all schema was made up to protect the innocent.

SELECT * FROM questions 
left join (
    select connections.otherstuff,answers.id,answers.text from connections 
    inner join answers ON  connections.username2 = answers.username
    where connections.username1 = 'mikha'
 ) conn on questions.id = conn.id

http://sqlfiddle.com/#!2/c7252/1

It seems I need the full scheme to explain more. I'm fed up with this query so I will post the full scheme and explain everything later. Thanks :) — Michael Samuel, Dec 09 '12 at 02:18

MYSQL unknown clause join column in next join

2 Answers2

Linked