We had a server hard drive crash recently, and the OS reload led to mySQL being upgraded from V4 something to 5.0.26 without plan :(
("Downgrading MySQL from 5 to 4 will be a non-trivial exercise", says the admin, "find another solution" :( )
I'm finding that queries that were working perfectly well before, now
aren't. We were able to save all data, it was only the versions of installed software that changed.
The query appears to be having a problem with the ON clause of a LEFT OUTER JOIN
eg.
SELECT *
FROM EmploymentRecords AS er, Employees AS e, user AS u
LEFT OUTER JOIN restaurants AS r ON r.idRestaurant = er.idRestaurant
WHERE u.idPerson = e.idPerson
AND er.idEmployee = e.idEmployee
AND u.idUser = 'john.smith'
ORDER BY er.dtStarted DESC
LIMIT 0 , 100
MySQL said:
#1054 - Unknown column 'er.idRestaurant' in 'on clause'

*but!*

SELECT *
FROM EmploymentRecords AS er, Employees AS e, user AS u
WHERE u.idPerson = e.idPerson
AND er.idEmployee = e.idEmployee
AND u.idUser = 'lea.de.groot'
ORDER BY er.dtStarted DESC
works fine

and
SELECT *
FROM EmploymentRecords AS er
LEFT OUTER JOIN restaurants AS r ON r.idRestaurant = er.idRestaurant
works fine

(ER:
An EMPLOYEE will:
- have one USER record, continaing username and password, (1 - 1)and
- have an EMPLOYMENTRECORD for each time they are employed or change
restaurants (1 - many))

I am quite bemused by what could be causing this.
Anyone have any idea?
(Help!)