MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Ryan Allbaugh Date:December 19 2005 9:41pm
Subject:Unable to reference right join table in left join statement under windows...but works under linux
View as plain text  
I am using MySQL 5.0.15 on windows and cannot run this query:

SELECT a.*,b.name, c.fullname,d.fullname
FROM access_authorization a, building b
LEFT JOIN users c ON a.createdby=c.id
LEFT JOIN users d ON a.modifiedby=d.id
WHERE a.sortcode=b.sortcode AND
  a.sortcode like '1,2,1,6%' LIMIT 0, 25

I receive:
ERROR 1054 (42S22): Unknown column 'a.createdby' in 'on clause'

But this query DOES work under my linux mysql 5.0.0-alpha!

I can modify the SQL Statement to the following and it works fine:

SELECT a.*,b.name, c.fullname,d.fullname FROM access_authorization a
LEFT JOIN users c ON a.createdby=c.id
LEFT JOIN users d ON a.modifiedby=d.id
RIGHT JOIN building b ON a.sortcode=b.sortcode
WHERE a.sortcode like '1,2,1,6%' LIMIT 0, 25


But I have a lot of SQL statements like this and I do not want to have
to modify them all.  Does anyone have any ideas on what is wrong?

I've been able to reproduce the problem with some generic tables, so I
wont include my table definitions here.
Thread
Unable to reference right join table in left join statement under windows...but works under linuxRyan Allbaugh19 Dec
  • Re: Unable to reference right join table in left join statement underwindows...but works under linuxSGreen19 Dec