Nested query problem - ERROR 1054 (42S22): Unknown column 'users.id' in 'on clause'

[Joachim Bartosik]

I had a pretty long query that worked on Postgres but doesn't work on MySQL. (Very) simplified query reproducing my problem is

SELECT * FROM users WHERE EXISTS (SELECT * FROM questions JOIN answers ON answers.owner_id = users.id);

I results in

ERROR 1054 (42S22): Unknown column 'users.id' in 'on clause'

Note: I can make the simplified query work by changing it to

SELECT * FROM users WHERE EXISTS (SELECT * FROM questions JOIN answers WHERE answers.owner_id = users.id);

However that's not possible for my original query - I'm making LEFT OUTER JOIN with a few conditions and select only records where answers.id IS NULL.

In case it matters I'm using MySQL 5.1.46 on Gentoo Linux. Could you help me to fix this?



Edited 1 time(s). Last edit at 07/30/2010 07:32AM by Joachim Bartosik.