0

After updating mysql from 4 to 5, I'm now getting this error:

Invalid SQL: SELECT HRS.Hrs AS Val, HRS.Job_ID, TS.Staff_ID, Staff.First_name, Staff.Last_name FROM Timesheets AS TS, Timesheet_hrs AS HRS LEFT OUTER JOIN Staff ON (TS.Staff_ID=Staff.ID) WHERE TS.ID=HRS.Timesheet_ID ORDER BY Staff.First_name, Staff.Last_name, HRS.Job_ID DESC:
1054 (Unknown column 'TS.Staff_ID' in 'on clause')

1054 is a known issue:
http://bugs.mysql.com/bug.php?id=13551

I've tried wrapping the FROM conditions in brackets, but it doesn't fix the problem entirely.
Since I'm a mysql noob, would anyone be kind enough to help translate the SELECT line into a working mysql5 command?

cheers

I'll update this as soon as I have a working fix

1
Contributor
1
Reply
2
Views
5 Years
Discussion Span
Last Post by rom.
0

Right, still not entirely sure how this works, but I'm told it needed to be intrusive, not extrusive..? Also, something about commas have a lower value than JOINs.. In anycase, heres a working version.

VERSION mySQL 4

	SELECT $val_select HRS.Job_ID, TS.Staff_ID, Staff.First_name, Staff.Last_name 
	FROM Timesheets AS TS, Timesheet_hrs AS HRS
	LEFT OUTER JOIN Staff 
	ON (TS.Staff_ID = Staff.ID) 
	WHERE TS.ID = HRS.Timesheet_ID
	ORDER BY Staff.First_name, Staff.Last_name, HRS.Job_ID DESC

VERSION mySQL 5

	SELECT $val_select HRS.Job_ID, TS.Staff_ID, Staff.First_name, Staff.Last_name 
	FROM Timesheets AS TS 
	JOIN Staff ON TS.Staff_ID = Staff.ID
	JOIN Timesheet_hrs AS HRS ON TS.ID = HRS.Timesheet_ID
	ORDER BY Staff.First_name, Staff.Last_name, HRS.Job_ID DESC
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.